Formula to include document path based on a cell value.

**klturi421** · 11-20-2014, 07:39 PM

I have not successfully performed this yet, however, I am wondering if it is possible to have a formula that can use a cell value to locate a specific file within a predetermined folder.

I'm trying to use an INDEX MATCH formula to achieve my results. Each person has a specific xlsx/xlsb file within a folder called 'agent'.

The formula I am using right now is using:

Formula:

Please Login or Register  to view this content.

But I would like to replaced 'david' with something like a cell number (A3 for example) so that when I drag out a formula it will automatically search for the file with that name. It would basically be searching for david.xlsb without me having to specify david in that particular formula. In theory the formula will be utilized just under 100 times within the document.

**FDibbins** · 11-20-2014, 07:48 PM

To refer to a text cell to be used as part of a formula, the way you want, you would use INDIRECT(). However, that only works on open files, and it appears that your file will be closed?

You have 2 options for that...
1. try using the Morefunc add-in which allows INDIRECT() to work on closed workbooks.
2. get someoner to write some VBA code for you to do what you want