Reference ever changing file name, last occurance

[288enzo]

Hi again,

I have a formula in one excel file that references a separate excel file. This issue is the file that is referenced changes each time it's downloaded to something like "file_name_MMDDYYYY". I found something similar -https://www.mrexcel.com/board/threads/referencing-file-to-open-with-partial-name-changing.1177202/#post-5725984, which would work; however how do I get around having multiple versions of the file that is referenced? What I really need to be able to do is reference the last saved occurrence of "file_name_".

To keep it simple, as an example my formula would be

=IF([Has_Has_Not_Taken_Report_09172021.xlsx]Sheet1!$E$191="incomplete",TRUE,FALSE)

Can what I'm asking even be done? I'm trying to avoid making sure those who use this are using the last instance of the file.

 

[ClaireS]

You can use Power Query (Excel Get and Transform) to Get all files in a folder. When it lists the files, Transform the data. You could then use the "Date modified" or "Date created" property (which are columns in Power Query) and filter for IsLatest. Then remove all columns but the filename, so that when you Close and Load, you are populating a single cell with the filename you require. Then use that in your formula. You will need to Refresh the query to get an up-to-date result, but you can set PQ to refresh when you open a file, or every n minutes.