adding indirect values

[uk747]

currently have
=INDIRECT("'"&A1&"'!"&"C10")

which shows the sheet name in A1 and the value in C10
If i wanted to add C10+C20+c32 whats the easiest way or if a range i.e. C10:C16

DO i have to use =INDIRECT("'"&A1&"'!"&"C10") +INDIRECT("'"&A1&"'!"&"C10")+=INDIRECT("'"&A1&"'!"&"C32")

 

[Jonmo1]

Try

=SUMPRODUCT(SUBTOTAL(109,INDIRECT("'"&A1:G1&"'!C1:C16")))

There cannot be any blanks or invalid sheet names in A1:G1


And if those are still dates that correspond to sheets named 1 May etc.
=SUMPRODUCT(SUBTOTAL(109,INDIRECT("'"&TEXT(A1:B1,"d mmm")&"'!C1:C10")))

 

[uk747]

Try

=SUMPRODUCT(SUBTOTAL(109,INDIRECT("'"&A1:G1&"'!C1:C16")))

There cannot be any blanks or invalid sheet names in A1:G1


And if those are still dates that correspond to sheets named 1 May etc.
=SUMPRODUCT(SUBTOTAL(109,INDIRECT("'"&TEXT(A1:B1,"d mmm")&"'!C1:C10")))

never even heard of the subtotal never mind the 109 part. is the subtotal applicable if you want to add a range (C1:C10) or a Cell C10.

or is subtotal used/needed when grouping/adding consecutive sheets


normally its just =SUM('4 May:30 May'!A1) so thought the indirect might be along those lines

 

[Jonmo1]

The subtotal is there to create an array of C1:C10 from each sheet named in A1:G1.
109 is the option in subtotal to make it do SUM (it can also do Average, Product, Count etc..)

Yes, it will work for either single cell C10 or a range C1:C10

 

[uk747]

ok thanks, so no other way other than subtotal to make indirect equal =SUM('4 May:30 May'!A1)

 

[Jonmo1]

ok thanks, so no other way other than subtotal to make indirect equal =SUM('4 May:30 May'!A1)

I don't know if there is or is not another way.
But that's the way I know how to do it.