Pulling Numbers From Your Other Sheets
Okay. Last lesson we figured out what you actually want to see first thing when you open your spreadsheet. A dashboard tab, sitting up front, showing you the stuff that matters.
Problem is, right now that tab is empty. Or it's got numbers you typed in by hand, which means it's already out of date. Today we fix that. We're gonna pull real, live numbers from your inventory tab and your invoice tab straight onto your dashboard, so it updates itself.
This is my favorite lesson of the whole course, honestly. It's where the spreadsheet starts feeling like it's working for you instead of the other way around.
The one function that does most of the work
You don't need anything fancy here. I know I keep saying that, but I mean it extra today. Ninety percent of what you want on a dashboard is just SUM, pointed at the right place.
Here's the basic move. Say your dashboard is on Sheet 1 and your inventory list is on Sheet 2, with quantities in column C.
Click the dashboard cell where you want your total item count to show up. Type:
`` =SUM(Inventory!C:C) ``
Swap "Inventory" for whatever your tab is actually named. Hit enter. If your inventory tab has numbers in column C, that cell just filled itself in with the total, and it'll keep itself updated every time you change something on that other tab.
That's it. That's the trick. One tab is talking to another tab now.
Doing the same thing for money
Same idea for your invoice totals. If you've got a tab of invoices with a total column, say column F, you'd do:
`` =SUM(Invoices!F:F) ``
Now your dashboard shows total invoiced, live, without you copying a single number by hand.
You can get a little fancier if you want, pulling just this month's invoices instead of everything ever, using SUMIF with a date range. I'll be honest, that one takes some fiddling to get the date format to match up right. If you want it, come find me after class and we'll do it together on your actual sheet. Easier to show than to explain in a paragraph.
Checking it's actually right
Here's where I make you do the boring thing. Before you trust that number, check it against reality.
Count some boxes. Look at your actual bank balance. Compare it to what the dashboard says. A spreadsheet will hand you a confident wrong answer without blinking, it doesn't know the difference between right and wrong, it just does what the formula says. I learned that one the hard way, early on, when a formula error overcounted a whole product line by about 300 units before my boss caught it. Not a fun afternoon. So now I check totals against a physical count every time, no exceptions, even when I'm sure the formula's fine.
You don't need to recount everything every day. But when you first build a pull like this, verify it. Once you trust it, you can relax a little.
A quick word about sharing this thing
If anyone else is going to look at or touch your dashboard, don't hand them your actual working file. Make a copy and share that instead. People will edit the wrong cell by accident, every single time, it's not malice, it's just what happens when two people are in the same sheet. Protect your master copy like it's the only one, because functionally, it is.
Making it not look like a spreadsheet threw up
Once your numbers are pulling correctly, and only once, you can clean it up a little. Bold your totals. Maybe box off the section. Nothing wild.
This is actually the same lesson I try to teach every single time someone's picking fonts before their formulas work. Function first, pretty last. I brought banana bread to the very first spreadsheet class I ever sat in on, back before I was the one teaching, and somebody said "oh, you didn't have to do that." I've brought snacks to every class since, because apparently once you decide that's just who you are, you don't really get to stop. Your dashboard's kind of the same deal. Once it's built right, you keep it up. It becomes just a thing you do.
Before next time
Get at least one live number pulling onto your dashboard from another tab, even if it's just the one. We'll build out the rest together next time, and it goes a lot faster once you've done it once.