EXCEL 2010 - LINKING TO OTHER FILES QUESTION
EXCEL 2010 - LINKING TO OTHER FILES QUESTION
(OP)
I was able to find how to link a cell in one file to a cell in another file. easy enough. However, what i would like to do is link that cell to a specific cell in another file, but that file would depend on what file is listed in another cell.
For example....
I have three files. KJS0001, KJS0002, & KJS0003. In cell B7...
KJS0001 has "Cats"
KJS0002 has "Dogs"
KJS0003 has "Birds"
In a new excel file, in cell G4 I will name one of those three files. Lets say I name KJS0002. In cell H4 I need it to tell me what is in B7 of whichever file is named in cell G4. In this case it would be "Dogs". But if I should open the file in the future and change G4 to KJS0003, I need cell H4 to automatically change to "Birds".
As I said, I can link a cell to another cell in another file, but I cant figure out how to get it to refer to a different cell to tell it which file to look into. Is this possible?
Thanks in advance to anyone who looks into this.
For example....
I have three files. KJS0001, KJS0002, & KJS0003. In cell B7...
KJS0001 has "Cats"
KJS0002 has "Dogs"
KJS0003 has "Birds"
In a new excel file, in cell G4 I will name one of those three files. Lets say I name KJS0002. In cell H4 I need it to tell me what is in B7 of whichever file is named in cell G4. In this case it would be "Dogs". But if I should open the file in the future and change G4 to KJS0003, I need cell H4 to automatically change to "Birds".
As I said, I can link a cell to another cell in another file, but I cant figure out how to get it to refer to a different cell to tell it which file to look into. Is this possible?
Thanks in advance to anyone who looks into this.





RE: EXCEL 2010 - LINKING TO OTHER FILES QUESTION
RE: EXCEL 2010 - LINKING TO OTHER FILES QUESTION
Use the INDIRECT() function to concatenate text values used to represent a link/range reference.
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: EXCEL 2010 - LINKING TO OTHER FILES QUESTION
Skip, I am not familiar with the INDIRECT function. I will have to look that one up and see how it works.
DavidBeach, I was trying a concatenate function and couldnt get it to work. If I link a cell to a particular cell in a different file, my code looks like this...
='[MAW02451S_001.xlsm]wORKSHEET-2'!$C$32:$E$32
I want the... MAW02451_001 ...to change according to what is in cell B5. I tried...
='[concatenate(B5,".xlsm")]wORKSHEET-2'!$C$32:$E$32
Didnt work.
='[=concatenate(B5,.xlsm)]wORKSHEET-2'!$C$32:$E$32
This will give me an OPEN dialogue box where I have to select the file. I cant get it to update automatically.
I have used different formulas in the past but I have no experience linking to other files. So I am lost on how to mix the two. lol
RE: EXCEL 2010 - LINKING TO OTHER FILES QUESTION
RE: EXCEL 2010 - LINKING TO OTHER FILES QUESTION
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: EXCEL 2010 - LINKING TO OTHER FILES QUESTION
RE: EXCEL 2010 - LINKING TO OTHER FILES QUESTION
...and drill down to any workbook, assuming that the data you're looking for is in a table.
Skip,
Just traded in my OLD subtlety...
for a NUance!