I have a formula in an Open Workbook (open.xlsx) where it is pulling from a Master File (closed.xls) in my Downloads folder. That Master File (closed.xls) i download every few days that has adds new values in column B, and dates in column A (i.e. i download it on 7/20/2023, it will have values through 7/20/2023....then if i redownload it on 7/31/2023, it will have values in Column B through 7/31/2023). I don't like to create actual embedded links in my Excel files (i.e. if i go to File -> Info, I don't want "external links" show up in the document).

In my open.xlsx file, i want to create a reference to this closed book that stays as a value if i close the master file (closed.xls). My current formula is:

=INDEX(INDIRECT("'C:\Users\DEFAULT\Downloads\[closed.xls]Sheet1'!$B:$B"),MATCH(A1,INDIRECT("'C:\Users\DEFAULT\Downloads\[closed.xls]Sheet1'!$A:$A"),1))
That formula works when closed.xls is open, but returns #REF when i closeout closed.xls. Is there a formula that incorporates INDEX/MATCH that keeps the value alive even if i close out of the Master File (close.xls)? I don't mind opening up close.xls to update the value in open.xlsx but i want the value to stay put if the worksheet closes. I was thinking maybe some sort of self-referencing IF function. I couldn't really find much by way of Google but below are two somewhat relevant threads but i haven't been able to gather the brain power to incorporate any of those suggestions into how my current INDEX/MATCH with INDIRECT function is working with closed.xls open.

https://stackoverflow.com/questions/...using-indirect
https://www.reddit.com/r/excel/comme...osed_workbook/