How do I Populate Data Across Tabs?

**uxevangelist** · 06-25-2009, 09:42 PM

I'm sure this question has been asked a gazillion times, so apologies if any of you will be beating a dead horse. I did some quick searches to try to find this question having already been asked but nothing glaring stuck out.

Anyway, I'm curious as to if there's a way to take data from cells in various tabs and manipulate those values to populate in a completely separate tab.

Example:

I have the following tabs: A, B, C, D, and E

Tabs A-D are exactly the same in their format, so cell C2 is going to be where I want to gather data from in all four cells, A-D, and add those values together and have the total show in a cell in tab E. I tried to do a simple = formula, but when keeping the cell live and trying to click cells in other tabs, it would populate the formula with the name of the spreadsheet and whatever cell I clicked on in another tab, except from within the tab I've written the formula in. So, let's say I clicked cell K2 in tab C, tab E would show the name of the whole file and then cell K2, but would populate the data from cell K2 in tab E, not tab C.

Is any of this making any sense? lol. You probably understood what I meant right off the bat. Anyway, thanks for your time. I'll try to describe this more clearly if I've managed to totally make this post FUBAR.

-Stephen

**kellyfspringer** · 06-25-2009, 09:51 PM

I sort of follow you, although, I'm not quite seeing where your problem is.
Unless otherwise noted, excel will always assume that a cell reference is to the sheet that the formula is entered, that's why it is not showing "E!" in your formula if the formula is on tab E, but the formula will still work. If you'd rather the formula contain the "E!", you can manually type it in, and the formula will still work (even though it is not needed).

This may be a silly question, but have you tried completing the formula, or did you stop when you kept seeing that? If you did complete the formula, what type of error are you getting?

**uxevangelist** · 06-25-2009, 10:12 PM

Hi, Kelly. Thanks for your response. Just a bit more detail, I've built a calorie and fat gram tracker spreadsheet. I have the following tabs: Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Misc Calculations

Sunday-Saturday have exactly the same spreadsheet layouts and functions. At the end of each day, my total number of calories is added up, as are my fat grams. Then, I factor in how many calories burned from any exercise and then I have one more cell that subtracts my burned calories from the total number of calories consumed.

My goal is to (if possible) create a formula in the Misc Calculations spreadsheet/tab that will add the total number of calories consumed from each day together. So, basically, the data to be added together will be pulled from the same cell of each spreadsheet/tab. I tried starting a formula in the Misc Calculations spreadsheet/tab and ended up with this:

='Calorie and Fat Gram Calculator'!K2+'Calorie and Fat Gram Calculator'!K2+'Calorie and Fat Gram Calculator'!K2

That was from typing = and then going to each tab and clicking on the cell I want to have data pulled from. Where I should have a numerical figure greater than 0, all I have is a 0. The next thing that comes to mind is... well, the cells I'm trying to pull data from have formulas in them to come up with the figures that show in the cell, so would that muck things up or would it pull from the result of the equation and work with that data?

-Stephen

**kellyfspringer** · 06-25-2009, 10:32 PM

Stephen,
It really shouldn't matter that there are other formulas on the totals that you need. Did you copy and paste that formula? It looks like it is referencing the same sheet. Can you attach a mock-up spreadsheet? I need to see it because I'm perplexed but not ready to give up.

Please save it as an Excel 2003, if you have 2007 (I can't open 2007).

Thanks!

**ffffloyd** · 06-25-2009, 11:23 PM

This sounds like what Excel calls a 3-D Reference.

If you look up "refer to the same cell or range on multiple sheets" in the help file it will explain it in detail.

For now, what you need to do is go to the cell on your Misc Calculations sheet where you want the sum to appear, type "=SUM(" then click on the first tab you want summed (eg, Sunday), hold down the Shift key and click on the last tab you want summed (eg, Saturday) then click on the cell you want to be summed in the sheet that is showing (which will be Sunday), press Enter and the correct formula will appear in Misc Calculations.

Codewise, if you wanted the sum of cells K2 to appear in any cell of Misc Calculations, you could write it as:

=SUM('Sunday:Saturday'!K2)