In Excel:
I currently build a text string using the CONCATENATE function drawing from different cell values. This text string represents the path to a file I wish to extract data from. I attempted to use the INDIRECT function to obtain the value of the reference that the text string refers to. However, this only works if the file which is being referred to is open; if it is closed, I get a #ref! error.
Any idea on how to use a reference represented as a text string to obtain the value?
The point is to create a dynamic file reference within a formula so that when file information is changed, the formulas will be updated.
i.e.
filename = x
formula: =’C:\Documents and Settings\Lawrence\My Documents\[x.xls]Sheet 1′!$G$9
The string part of the formula is pieced together with the CONCATENATE function (as stated above) using a cell reference for the filename.
After obtaining the string ‘C:\Documents and Settings\Lawrence\My Documents\[x.xls]Sheet 1’!$G$9 how can I use this to obtain the data from Column 9,Row G in file x?