Can Excel reference a cell value and include it in a file link?

Question

I have a bunch of links I need to make from 75 similar files with different file names and information. I need to find a way to have excel use the values in one of my columns and plug them into the file path to take the information from the right file.

For example:

I need the actual file referenced for information to be taken from to be referenced from a cell in my file

='C:\Documents and Settings\[XXXXXXX.xls]Data'!$E$6

XXXXXX is a cell value, is this possible?

Answer 1

Yes
orlandobi has a point, but the function is wrong, his function will open the file, not call cells from it, your answer is like this,
Your column with files is B, the A has the path for each file.
Means A2 equals C:\Documents and settings
B2 equals XXXXXX
then in C2 paste this
=INDIRECT( "'"&A2&"\["&B2 &".xls]Data'!$E$6" )
This will brings you the value of cell E6 in Data sheet in file XXXX in the folder specified
Notice that you have to open both files at first for this function to work.

And I have another solution using macros.
Mail me any further Qs

Enjoy my profile, I am the VBAXLMan

Answer 2

1

Answer 3

think you can try the "HLOOKUP" function - which looks up a specified value in a Horizontal lookup or the "VLOOKUP" function which looks up a specified value in a vertical setup.

It is tricky but based on the information you have supplied, those two Excel fucntions could be useful.

Answer 4

you may desire to place in writing the formula then click on the worksheet & cellular you prefer to reference - it would nicely be hassle-free, no count number if that's a seperate worksheet - ensure you have it open first & have the two living house windows exhibiting

Answer 5

Yes you can.
=hyperlink("c:\Documents and Settings\"&A1&"Data'!$E",A1) where A1 contains the filename.