How to automatically change path name for reference to most recent closed workbook

[Retroshift]

Hi,

I have two workbooks: one is closed and one is open. And I would like to extract data from the closed one into the open one.

I can return the value of cell A2 of the closed external workbook in my open workbook with the following formula: =INDEX('C:\path\[externalworkbook.xlsx]Sheet1'!A:A;2)

However, every day a new workbook with a different name (workbook name = date+time of export; e.g. MyRequests-20220518_160450) is generated into the file folder in order to represent the latest data. I want to use the most recent external workbook and I am able to identify it in my open workbook.

My question is how can I reference the identified most recent workbook name in the INDEX path? Are there VBA or non-VBA solutions besides the INDIRECT function? (I noticed that the INDIRECT function does not allow to get data from a closed workbook.)

Thanks