Microsoft Office Q&A: Ask anything (Word, Excel, PowerPoint, etc.)

Jack Hayes said:Exactly. I don't want to be pushy, but I think there's a flaw in how this is set up. 😂

@hollowmaker11/">@@hollowmaker11:
One shift ends with a specific final state for the Apens structure.
The next shift starts from that exact point and reaches its own unique end state. There shouldn't be any reason for the second shift to mess with the first shift's data.

It’s because money was being moved and handed out during the shift. Since there were expenses, the structure changes. This means the second shift has its own distinct Apens structure. However, when I input the total sum of all Apens from the first shift's closing balance into the second cell, it correctly shows the starting state, but as soon as I touch the Apens table, that value changes too. What formula can I use to lock that transferred amount? That figure should stay fixed; once new entries are made in the left table, the new total should become the closing balance. Closing-Received = turnover.

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:The link works perfectly

Funny enough, when I tried clicking that link yesterday, I kept getting those annoying "expired" messages and random error pop-ups. It was such a headache! But today? It’s working like a charm—smooth as silk. 🙂
I’m definitely going to make use of this document. I'll set up a custom keyboard shortcut for it, and hopefully, it'll be just as useful at my office job too...
THANKS SO MUCH, KYLE JOHNSON2
🙂

Jack Hayes said:Like I mentioned before, I still think there is a fundamental flaw in the logic—specifically regarding who is entering what, where, and when. There isn't a clear link between those two tables and the two shifts, so formulas won't fix anything if the data itself is disconnected. 🤷

I'll send it over via PP

hollowmaker11 said:sending it over via PP

WHY via PP? I don't do private messages for this stuff because then I feel obligated to fix it on my own time, and I'm just not feeling that.
The forum is public for a reason. Helping people out here helps everyone see the solution, especially when you show examples. Ever thought about how someone else might run into the exact same headache down the road?
If we solve it for you, we solve it for them too.

Looking at your setup—which is pretty chaotic, if I'm being honest (hard to tell since I don't know your whole workflow)—I didn't quite catch what you meant about it working fine a while back. Looks like someone set a specific Print area for one cell? My guess is the original creator was messing with some VBA macros. Maybe check an old backup of the file if you have one and look at the formulas there.

now for shift 2, the taken amount is 803.80 and it has to stay that way... try putting different items in the left table and you'll see the "taken" and "given" columns change. I don't want that "taken" number moving. It needs to be 803.80 regardless of whatever items I toss into the left table.

That's standard behavior if the cells are linked with formulas or references.
Organizationally, it's a mess. You should structure it differently. My advice? Use one Sheet for every single day (just identical tables all tied together with links/formulas).
That way, after a month, you'd just have a set number of sheets in one Workbook (covering all three shifts, though you could even manage the shifts within a single workbook)

It makes sense that the total changes when you enter new items, because there's a formula in that cell summing them up. Just throwing an idea out there—if that doesn't work for you, maybe someone with more free time can jump in.
Check out this example in the attachment, cheers

Kyle Johnson2 said:WHY use PM? I don't take problems via PM because then I feel obligated to work on them, and I don't want that.
The forum is public, and it’s better to provide help publicly so others can see the solution and use the examples. You might want to consider that other members could run into this exact same issue, whether now or down the road.
If you get help here, everyone benefits.

Looking at your spreadsheet—which seems pretty disorganized to me since I don't know the full context—I didn't quite catch what you meant about it being solved previously. I noticed someone set a specific Print area? My guess is the original author was using VBA macros. Maybe check an older backup of the file if you have one to see how the formulas were set up there.

This behavior is standard if cells are linked through formulas or external links.
From an organizational standpoint, it could be improved. I suggest using one Sheet per day (with identical tables linked by formulas/links).
That way, you would have a set number of sheets in one Workbook for the month (covering all three shifts; you could even manage all shifts within a single workbook).

It makes sense that the total changes when you enter new entries, because the cell contains a formula that sums those specific items. I'm offering a different approach, but if that doesn't work for you, perhaps someone with more free time can assist.
Check out the example in the attachment, best regards.

The table isn't poorly organized; it's mostly because I didn't send the whole thing. It wasn't my intention to withhold help, but this is an official document and I didn't want to post it publicly.

Don't feel like you are obligated to do anything. I asked for help; I didn't give an order.

Best,

One more attempt at this...
Look, since your starting cash from the first shift is constantly shifting during the second—you know, because you're either dealing with change or just processing sales—you really ought to try approaching it this way:

Look, just take the closing balance from the first shift and plug it directly into a cell in the second shift table. You know, use it as the opening balance for the next crew. It’s just common sense—keep it right there where you can actually see it so nobody has to go hunting around for the numbers.

Then, right underneath that, you’re going to go ahead and build out a second section of the table. For the first two columns, you’ll want to include... I was digging through some old boxes in my garage the other day—you know how it is, one minute you're looking for a screwdriver and the next you're knee-deep in nostalgia and dust—and I stumbled upon this old bill. It’s one of those vintage pieces, a real relic. It got me thinking about how much things have changed. You look at the money we carry around now versus what used to circulate back in the day... it’s a completely different world. Everything feels so digital and fleeting lately, doesn't it? You miss that tactile feeling of holding history in your hands. Just a random thought that hit me while I was cleaning up, but it really makes you wonder about what else we've lost to time. And their... I don't even know where to start with this. It's just... it's one thing after another lately. I was sitting there, staring at my screen, trying to make sense of everything, and it just hits you—how much of this is just unnecessary noise? You try to be precise, you try to get the details right, and then someone throws a wrench in the gears. It’s frustrating. Honestly, it's exhausting. Just one more thing on the pile.Look, I’m telling you, it’s right there in the third and fourth columns. Just look at them. Seriously. Login....meaning that is to say... Exit. And then there’s that fifth column... just sitting there. Honestly, I don't know what they were thinking when they set this up. It's like they completely overlooked the basic logic of the layout. Just a total mess. Status update. Just checking in on how things are looking. So, I was sitting there staring at my screen this morning—coffee getting cold, mind you—trying to make sense of this spreadsheet mess, and it hit me. You’re essentially trying to calculate the net total after you account for the cash coming in and the cash going out, right? Specifically when you're dealing with a fixed denomination. If you want to figure out what’s left over after adding your intake and subtracting your outflow for a specific bill type, you’ve got to get the math right in that sixth column. You aren't just adding numbers; you need to set up a formula that multiplies that specific count by the value of the bill itself. It's about finding that balance between what you started with, what you pulled from the drawer, and what you put back in. Just basic arithmetic, really, if you don't let the software trip you up. The current situation. Just... the state of things. Honestly, I don't even know where to begin with this mess. It feels like everything is just spiraling, doesn't it? One thing after another, and no one seems to have a handle on the steering wheel. I was sitting there this morning, staring at my coffee, thinking about how much has shifted lately, and it’s just exhausting. Everything is constantly in flux, and frankly, it's getting old. We need some clarity here, some actual direction, instead of this endless back-and-forth. It's frustrating. Truly. Take the value from that first column, right there, and just drag the formula down as far as you need to go. It’s pretty straightforward, really. I mean, if you can't figure out how to populate a column, we might have bigger problems on our hands than just some messy data. Just grab that initial cell and pull it down. Simple enough.

So, look, I’ve been chewing on this idea, and it really needs some fine-tuning before we roll it out. Here is the gist of what I'm thinking: instead of just taking the final count from the first shift and adding or subtracting from it to get the second shift's starting point—which feels a bit sloppy if you ask me—we should actually calculate the status by multiplying the number of bills by their specific denomination. It’s more precise. What matters most is that when you move everything over to the second shift's table, you aren't just moving a total sum; you need to transfer every single denomination, both the count and the value. We need to see the actual breakdown.

Look, just take this as a suggestion—something that might actually be useful to you if you’re willing to listen. I'm not saying I'm always right, but sometimes a little perspective goes a long way.

Greetings.

hollowmaker11 said:The spreadsheet isn't actually messy, mostly because I didn't even send you the whole thing.

(well, for me anyway, since I don't know all this stuff)

hope you can get this sorted out
later

I want to thank everyone who took the time to help me understand and resolve this issue.

A huge thanks to frozenheron7 for fixing everything; I really appreciate you stepping in to get my workflow back on track. 😍

hollowmaker11 said:I really want to thank everyone who took the time to help me out or even just tried to wrap their heads around what I was dealing with.

First off, a massive shout-out to frozenheron7 for actually getting this sorted for me. Seriously, I can't thank you enough—you saved my skin and let me finally get back to work without this headache hanging over me. 😍

Happy to help! 🙂

Hey, does anyone happen to know how I can REVERSE the data in a column,
so that the birth years show up first, followed by the month and day,
instead of having them laid out like this...
12/31/1991.
12/31/1990.
12/31/1990.
12/31/1977.
12/31/1972.
12/31/1962.
12/31/1962.
12/31/1946.
10/31/1988.
10/31/1987.
10/31/1977.
10/31/1976.
10/31/1968.
10/31/1964.
10/31/1958.
10/31/1957.
10/31/1955.
10/31/1955.
08/31/1984.
08/31/1984.
08/31/1984.
08/31/1979.
08/31/1976.
08/31/1975.
08/31/1973.
08/31/1971.
08/31/1958.

Because there's a period sitting right after the year, Excel is basically treating your data as a string—just plain old text. In a situation like this, I suppose you could try setting up a formula like this:

=RIGHT(A1,5)&MID(A1,4,3)&LEFT(A1,3)

Now, if you were to just strip away that period after the year, Excel would actually recognize the data as legitimate dates—which is much more practical, in my opinion, if you ever need to run calculations with them—and then you can simply display them however you want via Format Cells -> Custom -> yyyy.mm.dd.

So, I tried typing the formula directly into the row where all the formulas live... but it’s just not taking it, like it won't let me enter it at all
Just so you know, my Column C is where those dates are sitting—I swapped things around and used Column C instead of Column A like you did, unless there's something else I should be tweaking to make it work properly

Ethan Miller3 said:I tried typing the formula directly into the row where the formulas are supposed to be... but it just won't accept it.

What exactly do you mean by "won't accept it"? Like, you type the formula in and then what—does nothing happen, or is there an actual error message?

It works perfectly, but you’ve actually landed in the wrong cell. Leave that date column (Column C) alone; just click over to Column D and type your formula right there..
If you want to clear out the data in Column C later on, just highlight everything in Column D, hit copy, and then use "Paste Special" right over the top of what's already there (make sure you don't move to a different spot when you go to paste).
When that little menu pops up, just look for "Values" and select that (paste values).
And that's really all there is to it! From here on out, Column D won't be cluttered with formulas—it'll just show the final results.
Once you're done, you can go ahead and wipe out Column C with those old dates.

And nope, still nothing—he just dumped the formula straight into the first cell and called it a day.

I am operating under the assumption that the cell has been formatted as text.

Before you even attempt to enter a formula, you really ought to select the entire column first—go ahead and set Format -> Format Cells -> Number -> General—and only then should you proceed to type in your formula.

Finally nailed down that winning formula—it was a total mess with the text formatting, so I had to convert everything over to numbers first
But man, it looks like only handled the very first row
Any idea how I can get it to run through the entire column instead?

but he only managed to get that first row done
how can I make it apply to the whole column?

Good grief. Just copy that formula down the entire column and be done with it.

Edward Foster23 said:For crying out loud. Just drag that formula down the whole column.

Wait, you're telling me this column has 110,000 rows? Seriously?