MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Link value from another excel file, using filename in a cell


Posted by Erik Niemi on May 29, 2001 6:34 AM

Hello!

I want to link a cell value from another excel file and use a value in a cell to tell what file i want to get the value from.

The normal way to link a value can look like this =[testfile.xls]sheet1!$a$1

But i wonder if there are any way to replace the file name and use a cell reference instead?

My problem is that i want to link values from many different files and i want a number in a specifik cell to tell from what file it should get the data.


Posted by Aladin Akyurek on May 29, 2001 6:45 AM


Wouldn't

="["&A1&"]"&A2

work, where A1 contains the filename (e.g., testfile) and
A2 the cell address to use (e.g., Sheet1!$A$1)

Aladin

=========

Posted by Erik Niemi on May 29, 2001 7:00 AM

Noph it will just show the value of what you bind together. So instead of showing the value in the file and cell i want.

cell a1: testfile.xls
cell a2: sheet1!$A$1
cell a3: ="["&A1&"]"&A2

and the result will be: [testfile.xls]sheet1!$A$1

Posted by Aladin Akyurek on May 29, 2001 7:05 AM

ERIK Noph it will just show the value of what you bind together. So instead of showing the value in the file and cell i want. cell a1: testfile.xls

CHANGE THE FORMULA IN A3 TO:

=INDIRECT("["&A1&"]"&A2)

Aladin

============= Wouldn't : ="["&A1&"]"&A2 : work, where A1 contains the filename (e.g., testfile) and A2 the cell address to use (e.g., Sheet1!$A$1) : Aladin : ========= : Hello!

Posted by Erik on May 29, 2001 7:27 AM

Thanks Aladin

Im getting closer to a solution. But this metod only works if i have the file i want to get data from open. As soon as i close it i get a #referense! error in that cell.

Maybe i need some macro to do the work for me instead. ERIK : Noph it will just show the value of what you bind together. So instead of showing the value in the file and cell i want. : cell a1: testfile.xls cell a2: sheet1!$A$1 cell a3: ="["&A1&"]"&A2 : and the result will be: [testfile.xls]sheet1!$A$1 CHANGE THE FORMULA IN A3 TO: =INDIRECT("["&A1&"]"&A2) Aladin ============= : : Wouldn't

Posted by Aladin Akyurek on May 29, 2001 7:51 AM

Yep... (NT)

Im getting closer to a solution. But this metod only works if i have the file i want to get data from open. As soon as i close it i get a #referense! error in that cell. Maybe i need some macro to do the work for me instead. : ERIK