asked on

Reference another file by using value from a cell on current sheet

I have a excel file with a column called Country Name.

I want to create a cell that does a Vlookup on the 1st sheet of a file with that country name.

So the normal VLOOKUP formula would look something like this:

=VLOOKUP(E1,[UNITED_STATES.xls]Sheet1!$A:$B,2,0)

However, I want to have the "UNITED_STATES" replaced with the value in the country column lets say its cell A1 in this example.

How can this be done?

Thanks,

Adam E

ASKER CERTIFIED SOLUTION

zorvek (Kevin Jones)

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

zorvek (Kevin Jones)

Note that INDIRECT will NOT work on a closed workbook. The referenced workbook must be open at the time of the calculation.

Kevin

Adam Ehrenworth

ASKER

Thanks!

One more question... What if the other file's first tab isn't called Sheet1 is there an easy way to adjust the formula to always look at the first sheet?

Adam E

Adam Ehrenworth

ASKER

Anyway for it to be done with files that are closed?

zorvek (Kevin Jones)

No, because the only way to reference a tab is by the tab name.

A lot more is possible with VBA.

Kevin

Adam Ehrenworth

ASKER

Ok. This will work for now. I can devist VBA option if I have to but I was trying to avoid that today :)

Thanks!

Adam

Adam Ehrenworth

ASKER

got me most of what I needed but unfortunately scripting is necessary to exactly what I want.