How to make a reference to a cell of another sheet, when the sheet name is the value of a cell?

excel excel-formula worksheet-function
mr_georg picture mr_georg · Nov 3, 2008 · Viewed 40.7k times · Source

In excel 2007, I have a formula in a cell like the following:

=COUNTIFS('2008-10-31'!$C:$C;">="&'$A7)

Now I want to make the name of the sheet ('2008-10-31') be dependent on the value of some cell (say A1). Something like: 

=COUNTIFS(A1!$C:$C;">="&'$A7) // error

Is there is way to do this? Or do I have to write a VBA-Macro for it?

Answer

dbb picture dbb · Nov 3, 2008

INDIRECT does what you want. Note that if the sheet name has any spaces, you need to put single quotes round it, ie 

=COUNTIFS(INDIRECT("'" & A1 & "'!$C:$C"); ">=" & $A7)

Related questions

Convert date field into text in Excel

I have an Excel file which has a column formatted as date in the format dd-mm-YYYY. I need to convert that field to text. If I change the field type excel converts it to a strange value (like 40603). I tried …

Read More
Refer to a cell in another worksheet by referencing the current worksheet's name?

I plan to have a workbook with 24 sheets. The sheet names will be: Jan, Jan item, Feb, Feb item, etc. Basically it's for budgeting, with the month named sheet having summary info, and the ones with "item" in the name …

Read More
How to use VLOOKUP result as COUNTIF criteria

I want to be able to determine how many times a person's particular ID (e.g. #123) has appeared in a separate column (e.g. column C) given a name (e.g. Bob). I have: Let column A consist of several …

Read More