EXCEL 2010 - LINKING TO OTHER FILES QUESTION

[Kenja824]

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.

 

[davidbeach]

A nested IF or a CHOOSE with all three links should work. With a bit more work and you can build the link by concatenating the file name into the link equation.

 

[SkipVought]

HI,

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!}

 

[Kenja824]

Thanks for the help...

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

 

[davidbeach]

Oops, well I guess that doesn't work.

 

[SkipVought]

Do your concatenation within the INDIRECT() function.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Kenja824]

From what I am reading, it seems that INDIRECT to another workbook only works if that workbook is open. We could have hundreds of different files available and I need to get the info from a particular cell from whichever file I list in B5. It would be close to impossible to open all of the files so that I can just list one file in the cell and get its info. I need to extract the info without opening the file.

 

[SkipVought]

You might want to look at MS Query, via Data > Get external data > From other sources > From Microsoft Query > Excel files*
...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!}