Hi,
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?
From stackoverflow
-
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.
-
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)
mr_georg : Not only when it has spaces, but also other special characters like '-'Tomalak : That's true. I didn't think of that. +1
0 comments:
Post a Comment