Summing using column Heading

[Darin Kramer]

Howdie!

so situation is

Several Tabs (similar contents but different names) and a consolidated
Tab.
Within the Consolidated tab Cols A to Z each have a unique header (these
are the names of other tabs in the same book)
say Col A = Cat, Col B = Dog etc (ie we thus have a sheets entitled Cat,
dog, etc...)

In the consolidated tab I want to put a formulae In column A that says
sum, from (Tab entitled Cat) , rows 1 to 5. Now I could just say
=sum(Cat!a7:a10), but then I would manually have to change cat to dog
for each of my columns.
I really want to say sum(the contents of cell a1 on the consolidated
sheet!a7:a10) Any ideas???


Regards

D




*** Sent via Developersdex http://www.developersdex.com ***

 

[Bob Phillips]

=SUM("'"&INDIRECT(A1)&"'!A7:A10")

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Darin Kramer]

Thanks Bob doesnt seem to work - dont a need an open and close bracket
for the sum? also whats the first "" do?

Regards

D

*** Sent via Developersdex http://www.developersdex.com ***

 

[Stefi]

Try this:
=SUM(INDIRECT(ADDRESS(7,1,4,1,A1)):INDIRECT(ADDRESS(10,1,4,1,A1)))
regards,
Stefi


„Darin Kramer†ezt írta:

 

[Bob Phillips]

It would help if I got it right

=SUM(INDIRECT("'"&A1&"'!A7:A10"))

the "'" and "'! are just in case the sheet name has embedded spaces.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Darin Kramer]

AWESOME!!! you're a genius! Thanks!

*** Sent via Developersdex http://www.developersdex.com ***