How to convert text to a path

**KINEONAYVA** · 04-23-2011, 12:17 AM

What I am attempting to do is to have a cell with a value that I change then have a formula, macro or visual which would use this cell value to generate a path to a file to retrieve the value I want. If the value entered into the cell is 2910 then I would want to generate a path such as 'S:\Budget\Agency Budget\2011-12\[My 2910.xls]2910 FS'!$B$4. If I change the value in the cell to 1910 I would want the path to change to 'S:\Budget\Agency Budget\2011-12\[My 1910.xls]1910 FS'!$B$4 and pull that cell from that sheet. I am starting to think that this may be something I need to do in visual basic or macros but I am still holding out hope that I can do this without that. Let me know if you need any more information from me. THANKS!!!

**Leith Ross** · 04-23-2011, 01:36 AM

Hello KINEONAYVA,

Welcome to the Forum!

In the second example, it looks identical to the first example. Should the second example be this...

'S:\Budget\Agency Budget\2011-12\[My 1910.xls]1910 FS'!$B$4"

**KINEONAYVA** · 04-23-2011, 01:39 AM

The second one should in fact read 'S:\Budget\Agency Budget\2011-12\[My 1910.xls]1910 FS'!$B$4". Sorry for the error. Any ideas?

**Cutter** · 04-23-2011, 09:25 AM

What you want can be accomplished by one of the following:

If the file you want to reference is open you can use the INDIRECT() function:

http://www.excelfunctions.net/Excel-...-Function.html

BUT if the file(s) will be closed then you'll need the INDIRECT.EXT() function:

http://www.excelfunctions.net/Excel-...-Function.html

It is available in a free add-in called Morefunc which is available here:

http://xcell05.free.fr/morefunc/english

**KINEONAYVA** · 04-25-2011, 11:20 PM

I have tried various permutations of indirect and I cannot make it see the reference when it is varying the path not the rows or columns. Can you give an example of this in a small spreadsheet? Thanks for all of the help!

**Cutter** · 04-26-2011, 08:17 AM

Have you tried

=INDIRECT("'S:\Budget\Agency Budget\2011-12\[My "&cell&".xls]"&cell&" FS'!$B$4")

replace cell with the address of the cell holding the 2910 or 1910

Basically what you are doing is to enclose everything that remains constant within quotations and the parts that change are the cell addresses holding the values and are preceded and followed by the ampersand.