Using cell contents to identify sheet in function

GazNicki

New Member
Joined
Nov 30, 2010
Messages
48
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

Code:
=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

Well-known Member
Joined
Nov 7, 2006
Messages
8,328
Try

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

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

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,328
Doh! It should be this


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

GazNicki

New Member
Joined
Nov 30, 2010
Messages
48
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.
 

Forum statistics

Threads
1,085,036
Messages
5,381,317
Members
401,733
Latest member
Kabasa007

Some videos you may like

This Week's Hot Topics

Top