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 ***

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

--
HTH

Bob

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

"Darin Kramer" wrote in message
...

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 ***

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 ***

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


€žDarin Kramer€� ezt Ã*rta:



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 ***

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" wrote in message
...


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 ***

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

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