CODEWITHSUNDEEP
excelexcel-formulaworksheet-function
mr_georg
mr_georg

Reputation: 3713

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

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?

Upvotes: 10

Views: 40957

Answers (2)

dbb
dbb

Reputation: 2877

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)

Upvotes: 15

Tomalak
Tomalak

Reputation: 338118

You are looking for the INDIRECT worksheet function:

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

The function turns a string into a real cell reference.

Upvotes: 7

Related Questions