Using cell contents to identify sheet in function

GazNicki

Board Regular
Joined
Nov 30, 2010
Messages
56
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??
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
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,350
Doh! It should be this


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

GazNicki

Board Regular
Joined
Nov 30, 2010
Messages
56
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,233
Messages
5,594,963
Members
413,954
Latest member
mrsandy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top