Using cell contents to identify sheet in function

[GazNicki]

Hi

I have a Workbook with 54 sheets in it. I have a sheet for each week and they are named Wk (1) to Wk (52).

I have a sheet named "Calculus" that I wish to use to gather the data from each of the 52 sheets to monitor results throughout the year. Currently, I have the following sum in Calculus D4

=COUNTIF('Wk (1)'!$C$8:$P$9,Calculus!$C4)

Now, on Calculus D3:DC3 I have titles. These are Wk (1) to Wk (52) - the same as the sheet names.

Is if possible to update the function to look into the header cell to pick the correct sheet?

I have tried & but can't get it to work.

What I am looking for is a way to change 'Wk (1)'! to D3 in the function. Is this possible??

 

[Special-K99]

Try

=COUNTIF("'"&INDIRECT(D3)&"'!$C$8:$P$9",Calculus!$C4)

Note the range C8:P9 will not change above as it is within quotes

 

[GazNicki]

Thanks

This simply returns a #REF error unfortunately.

 

[Special-K99]

Doh! It should be this


=COUNTIF(INDIRECT("'"&D3&"'!$C$8:$P$9"),Calculus!$C4)

 

[GazNicki]

Many thanks for the help. I managed to make it copyable by moving the cell references out too, so the tidier formula is now:

=COUNTIF(INDIRECT("'"&E$3&"'!"&$C4),$D4)

Thanks for all your help. Pretty sure this will be useful for many many more occasions.