excel pick name of folder from cell

[mrineedhelpp]

Hello fellow members.

I have many excel files saved in one folder. These excel files have all the same format.

I want to extract certain information from specific rows and columns, so i started a new workbook and i wanted to be able to pick the file name from the list of file names i have in column A, like so in below image.

Any advise would be highly appreciated.

=INDEX('[(=A1).XLSM]'!$A$1:$R$31,23,4)

 

[fadee2]

hi mrineedhelpp,

Welcome to the board.

I'd suggest use of indirect function, inside your index function.

=INDEX(INDIRECT("["&B1&"]"&B2&"!"&B3),5)

indirect creates your range, and then that range is indexed to return a specific value.

This requires your origin workbooks to be open at the time of calculations, or else it will return #Ref error.

hth....

 

[mrineedhelpp]

Thank you for your feedback, this is feasible if I have a a few files.

However i have almost 400 files and every month they are renewed in a new folder.

Is there any other way where the workbooks do not have to be open?