Excel Question: Dynamic File names possible? situation, different, possible, Windows, example

manchester88

I have a sheet where I have many cells linking to many different excel files. I was wondering if it would be possible to create a formula where the file name changes depending on a cell. So for example:

Cell A1 has the word "Finance" in it.

Cell B1 pulls from C:\Windows\Program\"A1".xlsx

So in this situation it would be C:\Windows\Program\Finance.xlsx

If I change A1 to "Economics" then cell B1 will pull from C:\Windows\Program\Economics.xlsx

I've tried using & sings and concatenate but they don't seem to work correctly. I get extra quotation marks or something. Does anyone have any ideas?

dandman

You could put the VBA code into your personal workbook and have it open every time excel loads.

segalm

I feel like I'm in a Monty Python sketch...

xilinx_altera

Use INDIRECT

-------------------------------------------------------------------------------------

"Without American production the United Nations could never have won the war."
-Joseph Stalin, Tehran Conference: 1943

ruchita

You said, "INDIRECT only works if you have all of the files you are accessing open"

This is false, that's why you include the whole file path C:\Windows\Program\etc....

You should probably read some online tutorials regarding the INDIRECT function because we've essentially given you the exact formula to use and all you have to do is replace for your particular file.

comp_sci_kid

Thanks guys, but INDIRECT only works if you have all of the files you are accessing open. This is not possible in my case as there are many files I'm drawing the data from.

I want to avoid writing a macro for this because I just don't have the time right now. I can get to it later this evening, but using excel functions would just make things much easier.

Anyway, for some reason that I can't figure out right now, even if I have a file open INDIRECT is giving me a #REF error. When I click the Insert Function button to see what it's doing, the number it brings back is correct in the Ref_text box, but the result is showing the word Volatile and regardless of what I change in the A1 box, it still shows up as #REF. I'm still trying to play with this and see what the deal is.



Edited 1 time(s). Last edit at Tuesday, August 16, 2011 at 11:25AM by kant.

bodhisattva

Hey Kant,

You need to use the Indirect function to accomplish this; so there you would do:

Cell A1 = "ExcelFileName"

Cell B1 =INDIRECT("'"&"FilePath"&"["&$A$1&"]"&"TabName'!"&"Cell On Tab")


Now let me give you a real world example from one of my files:

Cell E35 = "Cost_Measures_08.11.xls"

Cell E36 = INDIRECT("'"&"C:\Users\453256\Desktop\NCR\"&"["&$E$35&"]"&"CM_Audits'!"&"C10")


Therefore, cell E36 will return the value of what is in cell C10 in the cost_measures file; if you change cell E35 to another file name that is in that folder, cell E36's value will be changed; hopefully this explains it.

yodacaia

You can use the indirect function to do this, take a look at the formula below

=INDIRECT("'"&"C:\Windows\Program\["&a1&".xlsx]Sheet1'!$G$8")

himanshumh

Basically, the problem I'm having with this is that the file name ends up like this:

'"C:\Windows\Program\Economics.xlsx"' when I use concatenate or &. I need to get rid of those stupid quotation marks so it just ends up like this:

'C:\Windows\Program\Economics.xlsx'

I think that will fix the problem, but I'm not sure. If anyone has any suggestions on how to fix this, or if there's another way to do it that will be great.

Thanks