All worksheet names are in format "mm-dd" throughout year.

Need to access worksheet for today's date... "today()", which means that I

have to format "today()" to "mm-dd" and find the worksheet.Formula must be in cell, not vba

=TEXT(TODAY(),"mm-dd") ----formats "today()"

How can I use the above formula as a worksheet name and grab a cell value?

=[text(today(),"mm-dd")]!n41 ----- doesn't work, excel doesn't recognize the formula as a worksheet name... wrong syntax.

Try this: =INDIRECT("'" & TEXT(TODAY(),"mm-dd") & "'!N41")

Make sure you get the double-quotes and single quotes correct.

If it works, it's your job to come back to this thread and tell us why.

Click Here Before Posting Data or VBA Code --->How To Post Data or Code.

I can't tell you how much I appreciate this... I've got about 20 hours researching and trying different things. =INDIRECT("'" & TEXT(TODAY(),"mm-dd") & "'!N41")

This worked like a champ.

Thanks again.

But you haven't told us us whyit works.

Click Here Before Posting Data or VBA Code --->How To Post Data or Code.

I have never heard of the "INDIRECT" function before... am researching it to understand it better... but it seems that the "INDIRECT" command is basically a reference, not literal. If you have the text "B1" in cell "A1" then if you use the command " =indirect(a1) ", it returns whatever is in the cell "B1", because "A1" told it to reference "B1". If the formula had quotation marks " =indirect("a1") ", then the formula would return the literal cell contents, or in this case " B1 ". In MY case,

=INDIRECT(" ' " & TEXT(TODAY(),"mm-dd") & " ' !N41")

you have quotation marks within the parenthesis around the whole formula, and single quotations to clarify the reference of the worksheet, with double quotations inside the singles to encapsulate the worksheet formula, including an "&", to then stipulate reference to include the cell.

I don't know if I explained that correctly, haphazardly, or just plain missed it. I'm still trying to follow it clearly myself.

Scott

That's pretty much it. Nice job! INDIRECT simply takes a text string and converts it to a reference.

To denote actual text, such as the single quotes needed around a sheet name, or the "!N41", you put double quotes around it.

If you want Excel to return a value from a function, you nest that function within the INDIRECT function.

The fun part is concatenating it all together correctly to build a string that INDIRECT will recognize so that it doesn't return the dreaded #REF error.

Just be careful. I'll assume you added spaces in the formula you posted simply for readability, but be aware that spaces within the double quotes will be treated as spaces and the formula will probably fail with a #REF error.

Click Here Before Posting Data or VBA Code --->How To Post Data or Code.

Yes... I added spaces only for others to follow easier. Next Question is how to adapt the original formula to incorporate... COUNTIF(n67:n74,"*m*") or an IF(AND

If I'm not mistaken, the COUNTIF would go before the formula, but what about the range and focus?

=countif(INDIRECT(" ' " & TEXT(TODAY(),"mm-dd") & " ' !N67:n74,"*m*"") ???

First, drop the spaces before you confuse me, yourself and others. Second, think about what you are trying to do. The syntax for COUNTIF is:

COUNTIF(

range, criteria)How are you defining your

range? With the INDIRECT function, right?The

criteriahas nothing to do with the INDIRECT function, so you need to make sure you delineate the INDIRECT function so that COUNTIF can determine where therangereference ends and thecriteriabegins.You're close.

Click Here Before Posting Data or VBA Code --->How To Post Data or Code.

I believe the correct format for the formula would be: =countif((INDIRECT("'" & TEXT(TODAY(),"mm-dd") & "'!N67:n74”)),"*m*")

I tried an example using a ws name in place of the today() and it worked.

Thanks again for the help.

Well, your answer is sort of correct. It's obvious that you did not copy that formula directly from a worksheet because, as written, it probably won't work. In fact, I tried it on 2 different machines and it doesn't work.

Take a look at the double quotes after n74. They appear to be a different font or style than the rest of the quotes and Excel does not recognize them. When pasted into Excel, they end up looking exactly like the other quotes, but they aren't the same.

It took a little work to determine why Excel was throwing up an error, but it turned out to be those quotes.

In addition, while it won't impact the results you'll get (once the quotes are fixed) you have an extra set of parenthesis in your formula.

You don't need "double parenthesis" around the INDIRECT function.

The final version that I would use is this:

=COUNTIF(INDIRECT("'" & TEXT(TODAY(),"mm-dd") & "'!N67:N74"),"*m*")

Click Here Before Posting Data or VBA Code --->How To Post Data or Code.

Not sure why the double quotes were different, but I see why there is no need of double parenthesis. Thank you for the correction. I only just tried it a few minutes ago. Thanks again,

Scott

Ask Your Question

Weekly Poll