Forum Discussion
Build formula using date label for workbook link
I have been searching for how to use a column label, which is a formatted date, as an element to put that date into a workbook link.
| SKU | 2025-01 |
| 24-GR-2 | XLOOKUP("*"&$A2&"*",'[Sales by Item 2025-01.xlsx]Sheet1'!$F:$F,'[Sales by Item 2025-01.xlsx]Sheet1'!$I:$I,0,2) |
I want to be able to use the column label "2025-01" in the xlookup formula [Sales by Item "2025-01".xlsx]
Thanks ahead for your help.
Brad
2 Replies
If 2025-01 is a text value:
=XLOOKUP("*"&$A2&"*", INDIRECT("'[Sales by Item "&B$1&".xlsx]Sheet1'!$F:$F"), INDIRECT("'[Sales by Item "&B$1&".xlsx]Sheet1'!$I:$I"), 0, 2)
If it is a date formatted as yyyy-mm:
=XLOOKUP("*"&$A2&"*", INDIRECT("'[Sales by Item "&TEXT(B$1, "yyyy-mm")&".xlsx]Sheet1'!$F:$F"), INDIRECT("'[Sales by Item "&TEXT(B$1, "yyyy-mm")&".xlsx]Sheet1'!$I:$I"), 0, 2)
Note that this will only work if the workbook referred to is open in Excel - INDIRECT fails with closed workbooks.
Thank you very much HansVogelaar! It works brilliantly. I just couldn't get the punctuation right to make it work.
Brad
