Excel Question

[gbpfan1231]

I have an Excel spreadsheet with about 50 tabs. Every tab is the same and has multiple columns. I want a summary sheet that will add up two cells from each of the 50 tabs into my summary sheet. For example:

 

Sheet 1 Subtotal of column A is in Cell A30 = $500

Sheet 1 Subtotal of Column C is in Cell C30 = $200

Sheet 2 Subtotal of column A is in Cell A30 = $700

Sheet 2 Subtotal of Column C is in Cell C30 = $100

and so on..

 

I want my summary sheet to have a column (if it is easier it could go across columns) where it looks like the following:

 

Sheet 1 $700

Sheet2 $800

Sheet3

Sheet4

and so on.

 

Any suggestions??

[Bring Back Pat!!!]

In your example, you'd write the following in the cell where you want the totals:

 

=SUM(sheet1!A30 + C30)

 

then the next total cell would be the same, except with the name of sheet 2. You can actually just go in and click on the cells from different sheets and the correct format will show up, but I have a similar spread sheet here at work, and that's what mine looks like, and it works fine.

 

Good luck.

[gbpfan1231]

In your example, you'd write the following in the cell where you want the totals:

 

=SUM(sheet1!A30 + C30)

 

then the next total cell would be the same, except with the name of sheet 2. You can actually just go in and click on the cells from different sheets and the correct format will show up, but I have a similar spread sheet here at work, and that's what mine looks like, and it works fine.

 

Good luck.

I get what you are saying but when I copy the formula down 50 rows the formula would keep the Sheet1 and I would have to go into each of the 50 formulas and change the name of sheet1 to sheet2 and then sheet3 and so on. The names of these tabs are also not standard.

[Coffeeman]

I get what you are saying but when I copy the formula down 50 rows the formula would keep the Sheet1 and I would have to go into each of the 50 formulas and change the name of sheet1 to sheet2 and then sheet3 and so on. The names of these tabs are also not standard.

Your problem is starting to make my brain hurt. I had to do this once many years ago, but can't quite remember the trick; so I'll do the next best thing: click the following link for the best Excel message board I've found. No offense, but most of us here are neophytes compared to these experts:

 

Mr. Excel.com

 

good luck...

[Brentastic]

Pivot Tables

[Atlanta Cracker]

I get what you are saying but when I copy the formula down 50 rows the formula would keep the Sheet1 and I would have to go into each of the 50 formulas and change the name of sheet1 to sheet2 and then sheet3 and so on. The names of these tabs are also not standard.

 

I have found over the years that the simple manual labor of changing 50 cells is usually more time efficient than trying to figure out how not to manually change the 50 cells ... especially if you never do and then end up changing them manually anyway!

[PSULions]

You could try naming your tabs so it would be alot easier to reference them in the formulas that you are trying to build. Are you familiar with how to name the tabs as well as your cells on the tabs you are tabulate?

 

I am pretty good with Excel and could help you out if I saw what you are trying to build and the what you are trying to accomplish.

 

I could send you a PM if you are interested.

[gbpfan1231]

You could try naming your tabs so it would be alot easier to reference them in the formulas that you are trying to build. Are you familiar with how to name the tabs as well as your cells on the tabs you are tabulate?

 

I am pretty good with Excel and could help you out if I saw what you are trying to build and the what you are trying to accomplish.

 

I could send you a PM if you are interested.

I do know how to name them and consider myself an excel guru. I already did the maual work but there has to be a function or a formula that would have saved me 15-20 minutes. The main reason I am still digging is that I can see this scenario coming up again.