General discussion


Dynamic file referencing

By bails4 ·
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.


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?

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

by Topic In reply to Dynamic file referencing

Do You mean how can I get data from Column G, Row 9?

surely if you typed =then the file address as shown G9 you will obtain whatever is in G9

If you are looking for particular number than an if or even sumif function needs to be added with the address string.

Could you proivde an example what you are exactly trying to achieve, as the above description is not clear

Related Discussions

Related Forums