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&"*",'[S...
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