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,327
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,327
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,081,773
Messages
5,361,207
Members
400,617
Latest member
barron1

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top