Totaling the same cells from different sheets

[ADC76]

Hello,
I'm trying to make a sales tax spreadsheet for my business. I have the
spreadsheet broken up into 9 sheets -- each sheet being it's own city. On
the tenth sheet I would like to have a total of the sales tax collected from
each city. I thought I could do this by using this formula, but it's not
working out so great.
=SUM(SHEET1:SHEET9!G234)
This is just the total for one city. I will duplicate the formula
accordingly for each city listed on Sheet 10.
Any help would be appreciated.
ADC7

 

[Don Guillett]

??

 

[Gord Dibben]

Your formula is correct in that it returns the sum of G234 across all
sheets, so if G234 is the sales tax for each sheet then you are summing all
the cities, not just one city.

Works great to get the total sales tax.

If you want to see the sales tax for one city, simply

=Sheet1!G234

=Sheet2!G234

Or =INDIRECT("Sheet" & row(1:1) & "!G234")

Copy down to row 9


Gord Dibben MS Excel MVP

 

[ADC76]

Yes. I entered the formula into F7 on Sheet 10. =SUM(SHEET1:SHEET2!G234).
It gave me a "name" error. I don't know why or what that is.
ADC7

 

[ADC76]

OK. I think I may have figured out what I was doing wrong. All I want to do
is display whatever value is in Sheet 1 G234. I typed in =SUM(SHEET1!G234)
and got a "REF" error. I may be using the wrong function to do this. Sorry
for the confusion.
ADC7

 

[ADC76]

Gord,
Does it matter that Sheet 1 is named Santa Rosa? I tried what you said and
it did not work. I also tried =Santa Rosa!G234. I got the 'name' error.
ADC7

 

[Gord Dibben]

Simply =Sheet1!G234 should suffice.............no need for SUM

Do you have a space in the sheet name like Sheet 1

Then you would need to wrap in single quotes.

='Sheet 1'!G234

To see how Excel does it..........select a cell on sheet10 and type =

then switch to sheet 1 and select G234 and hit ENTER key.


Gord

 

[ADC76]

I solved the issue. I entered: 'Santa Rosa'!G234. Now it works great!
Thanks for your help

 

[Gord Dibben]

One more thing.

The sheet names in formulas are case-sensitive so SHEET1 may not be correct.


Gord