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

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?

Can Excel reference a cell value and include it in a file link?
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( "'"%26amp;A2%26amp;"\["%26amp;B2 %26amp;".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
Reply: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.
Reply:Yes you can.


=hyperlink("c:\Documents and Settings\"%26amp;A1%26amp;"Data'!$E",A1) where A1 contains the filename.