tanvirabid3
New Member
- Joined
- Oct 15, 2021
- Messages
- 13
- Office Version
- 365
- Platform
- Windows
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.
=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.