AUTOMATICALLY CHANGE INDEX REFERENCE PATH TO A CELL(CONTAINING THE NEW PATH DIRECTORY)

[Cyrille]

Hi guys,

I am currently working on a tool to efficiently print the logs of our production process. However, sad to say I am really stucked.
Initially, my idea was to be able to select the desired .xlsx file from a directory - which I was able to do using a macro.
Then be able to print the cell values from the "Data" sheet of that file. So I would need to be able to automatically change the reference of the INDEX function for this.
Is there a way to do this? Below is how my formula looks when I manually input the worksheet name on the formula but I would like to automate it by simply selecting the file because I would have to go through 20 to 40 sets of logs a day.

=IF(B2>1,INDEX([20220523.xlsx]Data!$A$1:$AG$2423,RIGHT($I2,LEN($I2)-1),28),"")

 

[BSALV]

with an indirect formula and in a cell the name of your file
Excel formula: Dynamic workbook reference | Exceljet

Map1
	E
24	20220523.xlsx
25	=IF(B2>1,INDEX(indirect("'[&e24&"]Data'!$A$1:$AG$2423),RIGHT($I2,LEN($I2)-1),28),"")
Blad2