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?
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
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")
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.
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.
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.
http://www.mrexcel.com/forum/showthread.php?t=76842 http://www.ehow.com/how_7195822_use-indirect-excel-external-spreadsheet.html http://www.mrexcel.com/forum/showthread.php?t=60912 http://answers.yahoo.com/question/index?qid=20110616021118AAdupAn http://www.excelbanter.com/showthread.php?t=88562 http://www.excelforum.com/excel-worksheet-functions/781349-problem-with-indirect-function.html That’s the first few I found. Like I said, INDIRECT will only work if both files are open. I’m working with several hundred files, this is not practical. I know Harlan Grove made the PULL command and why someone else made INDIRECT.EXT to work around the INDIRECT function. The problem with using VBA for this is that it must be installed every time and that is a pain. However, if it’s the only way then I guess I’m stuck.
Use INDIRECT
I feel like I’m in a Monty Python sketch…
You could put the VBA code into your personal workbook and have it open every time excel loads.