Indirect and vlookup - Date format worksheet name

[tanvirabid3]

I am trying to pull data from different sheets using the INDIRECT FUNCTION. I am trying to get the data in the sheet called “Summary”. The data is the prices of various commodities in different sheets, for example 15 July, 8 July and so on. I rename the sheets 15 July as 5. In the 15 July sheet (renamed 5), the prices are in column D. Column A has the names i.e. PTA, Copper, steel etc. Now in the summary sheet, suppose I want the data of PTA on 15 July in cell F10. I use the following INDIRECT FORMULA. The formula works correctly and perfectly in this scenario:
=VLOOKUP($A10,INDIRECT("'"&F$1&"'!$A$1:$d$200"),MATCH(F$2,INDIRECT("'"&F$1&"'!$a$1:$F$1"),0),0)
Here in cell A10 is the word Copper, cell F1 is 5, A1:D200 = data in the source sheets; Cell F2 is the date i.e. 15-July-2022; A1:F1 in the source sheets is the column titles.

However, I am trying to use INDIRECT when the sheet name is a date i.e. 15-July-2022, the formula does not work. I adjust the formula like this:
=VLOOKUP($A10,INDIRECT("'"&(("'"&TEXT(F$2,"dd-mmm-yyyy"))&"'!")&"'!$A$1:$d$200"),MATCH(F$2,INDIRECT("'"&(("'"&TEXT(F$2,"dd-mmm-yyyy"))&"'!")&"'!$a$1:$F$1"),0),0)
I get #REF!
Not sure what am I doing wrong? Would appreciate if someone can help. I have also attached the screenshot.

 

[Fluff]

How about

=VLOOKUP($A10,INDIRECT("'"&TEXT(F$2,"dd-mmm-yyyy")&"'!$A$1:$d$200"),MATCH(F$2,INDIRECT("'"&TEXT(F$2,"dd-mmm-yyyy")&"'!$a$1:$F$1"),0),0)

 

[tanvirabid3]

How about

=VLOOKUP($A10,INDIRECT("'"&TEXT(F$2,"dd-mmm-yyyy")&"'!$A$1:$d$200"),MATCH(F$2,INDIRECT("'"&TEXT(F$2,"dd-mmm-yyyy")&"'!$a$1:$F$1"),0),0)

Thank you. This is fantastic.

 

[Fluff]

You're welcome & thanks for the feedback.