Posts by Jack Hayes

Casey Sanchez10 said:Like the title says, I really need some help moving a table from Word over to Excel. The guy who had this job before me built this whole massive table in Word—it’s about 30 pages of data now. I'm taking over the project, and honestly, I just find it so much easier to manage tables in Excel, so I'd love to migrate it, but I'm stuck on how to actually do it. 🙂 I tried using Paste Special, but the formatting turns into a total mess. The table has eight columns and as many rows as fit on a page; it's just plain text, no formulas involved. Does anyone know a better way? Thanks! 🙂

To be honest, it probably won't look identical to the version in Word. The main thing is making sure all the data is accurate and that every single bit of info lands in its "own" cell just like it was in Word. Once that's done, you can always play around with the styling to make it look right. Plus, once you're in Excel, you might realize you can pull out some of those repetitive values and swap them for actual formulas. It's a tiny bit of extra work upfront, but man, the payoff for how much easier your life becomes is totally worth it. 👍

frozenheron7 said:Should we be rounding things off, or could the result end up being something like an hour and a half?
For instance, if it's at 98%—would the answer be 3 hours, or maybe 1:45 (just throwing numbers out there here)

In this specific scenario, we’re missing the time dimension. We really need a start time for every piece of data entered—like a timestamp column. Then, the program could look at the system clock to calculate how much time needs to pass before that data point hits that "100%" mark.
Alternatively, we could set fixed intervals for when changes happen—say, a 5% increase every 3 hours, 6 hours, 9 hours, and so on. Even then, using the system time, you could calculate it; in that case, both 95% and 98% would see that same 5% bump at once, meaning they’d have the same window for change if they were logged at the same time—a maximum of 3 hours.

Gary Turner96 said:Hey everyone,

I've got a bit of a tricky question here, but I'll try to make it as simple as possible... (this is regarding Excel)

I'm wondering if it's possible to set up an Excel formula to calculate this:

If a player has ?% and earns 5% for every full 3 hours, how much time do they need to get from ?% up to 100%?

Basically, I want to be able to type any random number from 0-99 into cells A1 through A17, and have a formula in cells B1 through B17 automatically calculate the time needed to reach 100% starting from that initial percentage.

For example:

Player A1 starts at 70%. If they earn 5% every 3 hours, that means they need 18 hours total to hit 100%, since they need six more 5% increments to reach the goal...

Could anyone help me write a formula for this? Or, if it’s not doable in Excel, should I just whip up a tiny little program in C++ to handle it?

Thanks in advance for the help!

=((100-A1)/5)*3
100-A1 tells you how much is left to reach 100%. Dividing that by 5 ( /5 ) gives you the number of cycles, and multiplying that by 3 ( *3 ) gives you the total hours needed to hit 100%.
Computer Geek should probably know that one already. ☕

casualbear17 said:I am honestly losing my mind trying to put this content together in Microsoft Word right now. I can't for the life of me figure out where I'm messing up—I feel like I've tried doing this at least ten times already, and usually, everything goes perfectly fine.

So, I went ahead and manually tagged my headings and subheadings as Heading 1 and Heading 2, saved everything, and thought I was good to go. But then I tried to generate the Table of Contents, and it was a total mess! Instead of just showing my titles, it pulled in massive chunks of body text and entire tables that I definitely didn't mark as any kind of Heading. It's spread across several pages now—super frustrating!

I could really use some help here! 🙂I honestly have no idea what to do now. This is exactly how I've always approached my content creation, too. 🤷 .

I bet all those snippets of text definitely have their own unique style! Heading 1 I guess I should probably get started on this... Heading 2Just click that link and check it out once you land on the page. You can try switching the style to something else—maybe Paragraph or whatever works. It reminds me of that time a coworker brought over a document on a thumb drive to print; everything from the images to the tables just pulled right through. The whole thing was there. 🙂

Laura Martin29 said:I'm feeling the pressure over here—trying to finish my thesis and I feel like I've forgotten everything I ever knew about Excel 🙂🙂...Could someone please walk me through how to make a line graph for this table? Thanks so much!

Have you tried swapping out those periods for commas?
Like this:

You can actually use Adobe Acrobat - to print specific pages directly into a collection and then save them all as a brand new PDF.

Daniel Cook4 said:I'm really hoping I can finally manage to get my point across clearly this time. 🙂

So, I've got this classic Excel spreadsheet going, and I'm looking to add a little shortcut right next to a specific name—you know, kind of like those blue little icons or abbreviations you see sometimes. Any tips? www..I'm trying to get it set up so that when I click on them—you know, those shortcuts—it just opens up a specific Word document right away.
Example: Name: Joe Smith (Excel) - Shortcut: Contract

So, I've got this spreadsheet where I have names listed, and right next to them, I want a shortcut. Basically, when I click that shortcut, I want it to jump me straight over to the specific contract saved in Word. Any tips on how to set that up?
Is that actually possible? And if so, how do I go about doing it? 🙂

Thanks in advance!

To insert a hyperlink, just hit Ctrl+K. You can type out whatever text you want people to see, then simply pick the specific file you want to pop up when someone clicks that link.

Bradley Murphy56 said:Well, that last answer didn't quite hit the mark, so let me try breaking it down a bit more simply.

If I put some numbers in column A—say, row 1 is 1, row 2 is 0, row 3 is 3, row 4 is 0, row 5 is 5, row 6 is 0, row 7 is 7, row 8 is 0, row 9 is 9, and row 10 is 0...
And then in column B, from rows 1 through 10, I have 10, 20, 30, 40, 50, 60, 70, 80, 90, and 100.
What I really need to do is sum up only the values in column B where the neighbor in column A isn't zero. So, looking at those numbers, the total should be 250. How do I get Excel to actually do that calculation for me?

Formula:

Andrew Cruz3 said:I honestly don't think filters will work here 😬
...
since I have over 50 different services—each with its own price point—all crammed into a single column.
Some services consistently stay under a certain price, but those aren't the ones I'm looking for right now.

I can't really upload the spreadsheet here because it's got some pretty sensitive data in it.

You actually can make it work using Advanced Filters. Take a quick look at the tutorials on this page. Basically, you can set multiple criteria for the same column using "OR" logic, or specify exactly which services you want to include or exclude, along with price conditions for specific items. Just remember the golden rule: items on the same row follow "AND" logic, while items on different rows act as "OR" logic.

Andrew Cruz3 said:I could really use some help with this:

I've got a spreadsheet filled with these values
Client Type Service Price

I'm trying to pull out specific clients based on price categories—like everyone with a price under $333 for a specific service (there are about 50 of them). It would be awesome if I could also filter by Service Type, though I can just manually split up the types since there are only four.
The table has over 100K rows

I thought I could knock this out using a VLOOKUP formula combined with an IF statement, but I definitely bit off more than I could chew 😁
It's just not working. Help!

I messed around with a Pivot Table too, but it makes the layout a mess because of the huge number of services. Plus, it sorts everything alphabetically, which totally messes up my numerical flow.

Thanks in advance!

Try using Advanced Filter (Microsoft Office 2003). If you're using LibreOffice Calc, the Standard filter works perfectly for this.

redridge35 said:My Excel 2010 keeps turning numbers into dates. I know I can fix it by putting an apostrophe before the number or using format cells -> custom -> text, but then my formulas don't work right. Is there a permanent way to stop this?

What kind of number is it that triggers a date conversion? Usually, Excel only does that if you type something like '10-3' or '5-2'.
If you need that cell to actually work within your formulas, you really should just format the cell as a Number rather than setting it to Text.

frozenheron7 said:I don't have Word 2003 on my machine anymore either, but if I recall correctly, you can find it here:
Tools -> options -> view tab -> status bar

It sounds like you might have just turned off the status bar. Give that a shot and let me know if it works 😉

Yep, that did the trick!
In the American version, it's Tools -> Options -> View -> (Show) Status Bar

Carol Rivera32 said:Hey everyone! Thanks so much for all the help, you guys are lifesavers..
I'm reaching out because I've been stuck on an Excel issue for weeks now, so if there's a kind soul out there who can take a look..
So, here's the deal:

I have 10 different subjects and, say, 200 students.
I've laid out all ten subjects in columns, and students in rows.
The thing is, not every student has to pass all ten subjects—some just need to pass five, some four, others eight or seven, and so on. Basically, each student has a specific number of subjects they are required to pass (out of my total of 10).
Whenever a student passes an exam, I enter the date they took it (using this format: 1-Jan-13) into the cell under that subject's column. Exams a student still needs to take (but is required to) are marked with: "undefined date." Subjects a student isn't required to take at all are marked as "N/A."
So, the table with 10 columns and 200 rows is full of these labels (dates, "undefined date," and "N/A"), and every student has a different number of mandatory subjects.
What I really need is a formula that calculates the percentage of passed exams (relative to the total number of exams the student is actually required to pass—meaning I want to ignore the "N/A" cells)—for each row per student.
Because, naturally, I want that percentage to update instantly every time I punch in a new date..

For example, if someone has passed three exams (three cells with dates) and still has three left to go (three cells saying "undefined date"), that means they have 6 exams total to pass, making their pass rate 50%. So, Excel needs to completely ignore those 4 cells in the row that say "N/A."
What's the easiest way to do this (ideally in just one formula)?
Does anyone know?😕

I uploaded a sample file so you can see exactly what I'm working with: http://www.sendspace.com/file/2o4wqm

Since you have fixed text for all the columns—"N/A" means they don't take it, "undefined date" means they must take it, and then there's the actual date when it's passed—you can use a formula that just checks for those specific text strings and ignores the actual dates. Here's the formula:
Code:

Personally, I'd probably use something else instead of the phrase 'undefined date'—maybe just a dash, or 'xxx,' or something short. It would make things a lot cleaner.
Basically, this formula takes the total possible exams (which is 10), subtracts the ones that aren't required ("N/A") to find how many *must* be taken, then subtracts the number of exams not yet completed to get the passed count. Finally, it divides that by the number of required exams. Just make sure the column containing the formula is formatted as a percentage.

frozenheron7 said:Exactly. A date is really just a number—it’s actually the count of days since January 1, 1900, if I remember correctly—so how we choose to display it in a cell is entirely up to us. Personally, I usually stick to the method Nicholas King described to get the date back into a "standard" American format.

Since a date is fundamentally just a number, it's probably smarter to avoid using punctuation (like periods) within your formulas. Excel might handle it, but it doesn't always play nice.
I used this formula =DATE(MID(C1; 7; 4); MID(C1; 4; 2); MID(C1; 1; 2)) which is a surefire way to return a valid date. Once it's done, it'll show up using whatever the default Excel setting happens to be. No extra formatting steps required before or after.
Anyone can customize their date display, but what's actually standard practice? In formal American writing, you'd typically see July 17, 2012, or 07/17/2012 without those extra leading zeros or weird spacing. But there's a big difference between how you write a date in a document versus how it lives in an Excel column. I don't think seeing dates formatted like yyyy.mm.dd is common in Excel at all; that style feels much more suited for text files where you need things to sort correctly.

Amy Phillips40 said:Here's the issue: my Adobe Acrobat Reader XI keeps freezing every single time I try to open it (even though it was working perfectly fine until just recently). I tried reinstalling it, but no luck—it's the exact same thing. Not only can I not get my PDFs to load, but the app itself freezes up the moment I launch it. Any ideas? 😢

Maybe give a different PDF reader a shot. Personally, I've been using Adobe Acrobat Reader, but there are plenty of other options out there...

Ethan Miller3 said:I tried typing the formula directly into the row meant for formulas... but it won't take it.
By the way, I used column C for those dates (I swapped C for A just like you suggested, unless there's something else I should change)

Just plug in this formula: =DATE(MID(C1; 7; 4); MID(C1; 4; 2); MID(C1; 1; 2)) and it should convert to a date. If you don't like the default look, you can always change the cell formatting.

Oh, looks like this one's already sorted out...
Quick question though, what's up with that date format yyyy.mm.dd. using dots?!

hollowmaker11 said:It’s because she was swapping cash during her shift and handing money out to people, so there were actual expenses, which changes the whole structure. Basically, the second shift has its own specific petty cash setup. But here’s the headache: when I enter the total sum of all petty cash from the first shift's closing balance into the next cell for Shift 2, it correctly shows as the starting balance. However, the moment I touch the petty cash table, everything shifts again. Is there a formula I can use to lock that "transferred" amount? That transferred amount should stay fixed—I can't change it once it's set—but when I log new petty cash entries in the left table, I need that new total to become the final balance. Final Balance minus Taken equals Total Transactions.

Like I mentioned before, I still feel like there's a fundamental flaw in how this is being set up—basically, who is entering what, where, and when. If there's a total disconnect between these two tables and the two different shifts, no fancy formula is going to save you if the logic itself is broken.🤷

Kyle Johnson2 said:Those two example spreadsheets of yours would be worth their weight in gold if we weren't just spinning our wheels here.
Try putting a little more effort into this and show us an actual example of what you're aiming for—maybe include some comments to explain the logic, highlight the results, color-code things, etc. Be creative! 😉

Exactly. I don't want to be pushy, but I really think there's a flaw in the core concept here. 😂>

@hollowmaker11/">@@hollowmaker11:
One shift ends with a specific final state for a certain APENS structure.
The next shift starts from that exact point and then reaches its OWN unique final state for that APENS structure. Why on earth would the second shift need to mess around with the first shift's data?!

hollowmaker11 said:...
And that’s where I hit a wall

At the end of the second shift, when the crew is logging their inventory in the left table—which makes sense since they probably sold some items—the "Starting Balance" cell keeps getting messed up. My question is, what kind of formulas can I use to lock that starting balance in place? Basically, I want the starting state to stay fixed as the final amount from the first shift, even when the second shift starts messing with the numbers in the left column.

Thanks so much to anyone who takes the time to help me out with this!

Someone 🙂

Why would that be an issue?
The starting balance for the second shift should always match the closing balance from the first shift. To make sure this works perfectly, that cell needs to be a formula rather than just a typed-in number. That way, you won't have any discrepancies because they'll always be identical. For example, if the closing balance is in C2 and the starting balance is in D2, then D2 should just be the formula =C2.

Drew Newman5 said:Hey there,
I could really use some urgent help with this:
... the issue is that the data is numeric, but the decimal separator is a period instead of a comma, and it’s causing me a massive headache.
Basically, a lot of entries that should have been 2.05 turned into 2.svi—Excel basically decided they were dates. If I try to change the cell format to number, the value magically jumps to 41396.00 instead of going back to 2.05.

Any idea how to fix this?
...
🙂

If anyone knows a trick to solve this, I’d be super grateful...

The best way to handle this is to set all columns to Text format initially. Unless you have a specific date column in your CSV where you need exact date formatting, or a column with precise amounts where you want numbers, just stick to Text. You know, whenever you feel like Excel might start overthinking things and making its own rules, just force it to be Text. As for those amounts using periods instead of commas, just set the column format to Text, then use Find / Replace to swap the periods for commas, and finally switch the column format back to Number.

If you aren't doing a "Paste" but are opening the CSV file directly in Excel without getting those column format options, try renaming the file extension to .txt first, then open it with Excel.