ref wrksht name (format mm-dd) for today()

Microsoft Exchange server 2007 x64 dvd -...
December 28, 2010 at 09:39:20
Specs: Windows XP
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.


See More: ref wrksht name (format mm-dd) for today()

Report •

#1
December 28, 2010 at 10:16:33
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.


Report •

#2
December 28, 2010 at 14:25:15
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.


Report •

#3
December 28, 2010 at 19:14:42
But you haven't told us us why it works.

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


Report •

Related Solutions

#4
December 29, 2010 at 17:41:01
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


Report •

#5
December 29, 2010 at 17:53:17
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.


Report •

#6
December 29, 2010 at 18:29:35
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*"") ???


Report •

#7
December 29, 2010 at 18:53:00
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 criteria has nothing to do with the INDIRECT function, so you need to make sure you delineate the INDIRECT function so that COUNTIF can determine where the range reference ends and the criteria begins.

You're close.

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


Report •

#8
December 29, 2010 at 20:11:04
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.


Report •

#9
December 30, 2010 at 05:28:15
Thanks again for the help.

Report •

#10
December 30, 2010 at 06:28:54
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.


Report •

#11
December 30, 2010 at 11:20:34
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


Report •

#12
December 30, 2010 at 11:52:47
Keep learning!

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


Report •

Ask Question