"Indirect" with Date format worksheet name

there is one workbook with lots of worksheets, and each worksheet will use "mmm-yy"" (Feb-16) format as their name.

in another worksheet , I want to see the value of D19 in worksheet (name: Feb-16),

A1="Feb-16" (Date format, I got this by using "get.workbook")
B1=D19 (text format)


C1=INDIRECT(A1&"!"&B1) then will get "'#REF",
(if I change the name from "Feb-16" to "xxx"(or other non-date value), it will work......

did I do something wrong in formula at C1??? (if name is Date format) thanks !!!

What 'Exactly' is in A1?
Is it a real date, and you have the cell formatted to show as mmm-yy ?

Try
=INDIRECT("'"&TEXT(A1,"mmm-yy")&"'!"&B1)

Wooo, it is awesome !! thanks for sharing !!!

Originally Posted by Tony Valko

Try it like this...

=INDIRECT("'"&A1&"'!"&B1)

In "exploded view" (all those quotes makes it hard to read)...

=INDIRECT(" ' " & A1 & " ' ! " & B1)

Yes, I also consider that one before(using "Text" to convert it into text format value), but it didn't work until I added the little " ' " .

A1 has the name of worksheet by using "get.workbook", it has "date" format

Originally Posted by Jonmo1

What 'Exactly' is in A1?
Is it a real date, and you have the cell formatted to show as mmm-yy ?

Try
=INDIRECT("'"&TEXT(A1,"mmm-yy")&"'!"&B1)