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?作者: himanshumh 时间: 2011-10-12 00:21
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作者: yodacaia 时间: 2011-10-12 00:32
You can use the indirect function to do this, take a look at the formula below
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.作者: comp_sci_kid 时间: 2011-10-12 00:43
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.作者: ruchita 时间: 2011-10-12 00:49
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.作者: xilinx_altera 时间: 2011-10-12 01:00
