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
