Totaling the same cells from different sheets

A

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
 
G

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
 
A

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
 
A

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
 
A

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
 
G

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
 
A

ADC76

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

Gord Dibben

One more thing.

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


Gord
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top