Using VLOOKUP with a reference to a different file...

[beeblemonster]

OK. I made a brand new file because i think the last one was corrupted.
meh.

If I put...
=VLOOKUP(A4,'[file name (Updated 1august 2008).xls]Sheet 1'!$A:$Y,2)
in cell B4, then it works

If I put...
=VLOOKUP(A6'\\folder\folder\folder\folder\folder\[file name (Updated 1august
2008).xls]Sheet 1'!$A:$Y,2)
in cell B6, then it works

BUT IF I PUT...
'[file name (Updated 1august 2008).xls]Sheet 1'!$A:$Y,2)
in A1,
and
=VLOOKUP(A4,INDIRECT(E16),2,0)
or
=VLOOKUP(A4,INDIRECT.EXT(E16),2,0)
in B5... it gives back #REF!

WHAT AM I DOING WRONG????!!!

there is definately a match there because i can do the entire formula within
A3 and it works. something between cells A1 and C3 is not working. #REF!
still comes up when I add a 0 or false to the end.

What do you have in A3? Does it actually match with something in
column A of the external file? The way your VLOOKUP formula is set up,
the data in the external file needs to be sorted on column A, and if
A3 is empty or smaller than the first value then you will get an
error. You might like to look for an exact match, by changing your
formula to:

=VLOOKUP(A3,INDIRECT(A$1),15,0)

and then the data doesn't have to be sorted.

Hope this helps.

Pete

no. File name and Sheet 1 are replacements of what I have. I can't really
send that information out. But sheet 1 is replaced by the name of my first
tab and file name is exactly as the file name is. [bla bla bla (Updated
aug08).xls]



:
beeblemonster wrote:
It's still not working.
The file being called upon is open behind it. I have '[file name.xls]sheet
1'!$A:$X in A1. and C3 is showing... #REF! with =VLOOKUP(A3,INDIRECT(A$1),15)
as the code.

I can't find the mistake anywhere...

Does A1 actually say "file name" and "sheet 1", or did you substitute the file
name and sheet name you actually are trying to reference.- Hide quoted text -

- Show quoted text -

 

[Peo Sjoblom]

You can't have the apostrophes in the cell nor the range, you can use it
like this


=VLOOKUP(A2,INDIRECT("'"&A1&"'!$A:$Y"),2,0)

Where you would have the open filename and the worksheet in A1
and the first apostrophe concatenated with the ampersand before and after A1
then a text representation of this !$A:$Y

That works for me, note that indirect formula will never adapt to when you
close the file since it is a text string



--


Regards,


Peo Sjoblom

OK. I made a brand new file because i think the last one was corrupted.
meh.

If I put...
=VLOOKUP(A4,'[file name (Updated 1august 2008).xls]Sheet 1'!$A:$Y,2)
in cell B4, then it works

If I put...
=VLOOKUP(A6'\\folder\folder\folder\folder\folder\[file name (Updated
1august
2008).xls]Sheet 1'!$A:$Y,2)
in cell B6, then it works

BUT IF I PUT...
'[file name (Updated 1august 2008).xls]Sheet 1'!$A:$Y,2)
in A1,
and
=VLOOKUP(A4,INDIRECT(E16),2,0)
or
=VLOOKUP(A4,INDIRECT.EXT(E16),2,0)
in B5... it gives back #REF!

WHAT AM I DOING WRONG????!!!

there is definately a match there because i can do the entire formula
within
A3 and it works. something between cells A1 and C3 is not working. #REF!
still comes up when I add a 0 or false to the end.

What do you have in A3? Does it actually match with something in
column A of the external file? The way your VLOOKUP formula is set up,
the data in the external file needs to be sorted on column A, and if
A3 is empty or smaller than the first value then you will get an
error. You might like to look for an exact match, by changing your
formula to:

=VLOOKUP(A3,INDIRECT(A$1),15,0)

and then the data doesn't have to be sorted.

Hope this helps.

Pete

On Aug 14, 6:26 pm, beeblemonster
no. File name and Sheet 1 are replacements of what I have. I can't
really
send that information out. But sheet 1 is replaced by the name of my
first
tab and file name is exactly as the file name is. [bla bla bla
(Updated
aug08).xls]



:
beeblemonster wrote:
It's still not working.
The file being called upon is open behind it. I have '[file
name.xls]sheet
1'!$A:$X in A1. and C3 is showing... #REF! with
=VLOOKUP(A3,INDIRECT(A$1),15)
as the code.

I can't find the mistake anywhere...

Does A1 actually say "file name" and "sheet 1", or did you
substitute the file
name and sheet name you actually are trying to reference.- Hide
quoted text -

- Show quoted text -