INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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.

RE: EXCEL 2010 - LINKING TO OTHER FILES QUESTION

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.

RE: EXCEL 2010 - LINKING TO OTHER FILES QUESTION

HI,

Use the INDIRECT() function to concatenate text values used to represent a link/range reference.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: EXCEL 2010 - LINKING TO OTHER FILES QUESTION

(OP)
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

RE: EXCEL 2010 - LINKING TO OTHER FILES QUESTION

Oops, well I guess that doesn't work.

RE: EXCEL 2010 - LINKING TO OTHER FILES QUESTION

Do your concatenation within the INDIRECT() function.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: EXCEL 2010 - LINKING TO OTHER FILES QUESTION

(OP)
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.

RE: EXCEL 2010 - LINKING TO OTHER FILES QUESTION

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close