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

Hello.

I'm using VLOOKUP to grab data from a different file... that part is pretty
simple. The problem is the file name gets changed a lot. I need to have one
cell that has the path, and somehow get VLOOKUP to reference it's table_array
portion to that cell's path. that way instead of changing the 532 formulas
everytime someone changes the file name I can just change that one cell.

I'm having trouble getting this to work, I think the problem is with telling
it to look at the first tab but i'm not entirely sure.

please help!!!!!

On Aug 13, 10:02*am, beeblemonster
wrote:
Hello.

I'm using VLOOKUP to grab data from a different file... that part is pretty
simple. The problem is the file name gets changed a lot. I need to have one
cell that has the path, and somehow get VLOOKUP to reference it's table_array
portion to that cell's path. that way instead of changing the 532 formulas
everytime someone changes the file name I can just change that one cell.

I'm having trouble getting this to work, I think the problem is with telling
it to look at the first tab but i'm not entirely sure.

please help!!!!!

change the table_array portion to an indirect function. If you place
the filename in cell A1, then make your formula
VLOOKUP(B1,INDIRECT(A1),2). Remember to place the filename in the
format of something like:
C:\Documents and Settings\tsides\My Documents\[Book1.xls]Sheet1'!$A
$2:$B$5

You could even hardcode the path and just put the filename in A1:
VLOOKUP(B1,INDIRECT("C:\Documents and Settings\tsides\My Documents\
["&A1&"]Sheet1'!$A$2:$B$5"),2)

The won't work since INDIRECT does not work with files that are closed so
the only way is to open the file and thus the path is not necessary just the
file name.

If the OP does a Google search for Morefunc he can download and install that
add-in and one of the functions in that add-in is called INDIRECT.EXT which
will work with closed files

--


Regards,


Peo Sjoblom

wrote in message
...
On Aug 13, 10:02 am, beeblemonster
wrote:
Hello.

I'm using VLOOKUP to grab data from a different file... that part is
pretty
simple. The problem is the file name gets changed a lot. I need to have
one
cell that has the path, and somehow get VLOOKUP to reference it's
table_array
portion to that cell's path. that way instead of changing the 532 formulas
everytime someone changes the file name I can just change that one cell.

I'm having trouble getting this to work, I think the problem is with
telling
it to look at the first tab but i'm not entirely sure.

please help!!!!!

change the table_array portion to an indirect function. If you place
the filename in cell A1, then make your formula
VLOOKUP(B1,INDIRECT(A1),2). Remember to place the filename in the
format of something like:
C:\Documents and Settings\tsides\My Documents\[Book1.xls]Sheet1'!$A
$2:$B$5

You could even hardcode the path and just put the filename in A1:
VLOOKUP(B1,INDIRECT("C:\Documents and Settings\tsides\My Documents\
["&A1&"]Sheet1'!$A$2:$B$5"),2)

Thanks; I have many uses for that that add-in myself!

It's not working at all....
would it have something to do with the fact that both files aren't on my
hardrive... they are on a company server in password protected folder?

I've been trying to get this to work for hours... with and without it open.

Post the formula(s) that you have tried, and tell us what error
message you are getting.

Pete

On Aug 13, 10:00*pm, beeblemonster
wrote:
It's not working at all....
would it have something to do with the fact that both files aren't on my
hardrive... they are on a company server in password protected folder?

I've been trying to get this to work for hours... with and without it open.

I'ver tried it a couple of ways...

It says #NAME? when I dothis...

This is what C3's code looks like...
=VLOOKUP(A3,INDIRECT(A1),15)

and this is what A1's looks like...
=HYPERLINK('[file name]Sheet 1'!$1:$65536)

when I change A1 to \\folder\folder\folder\[file name]Sheet 1'!$1:$65536
it come with a #REF! error









"Pete_UK" wrote:

Post the formula(s) that you have tried, and tell us what error
message you are getting.

Pete

On Aug 13, 10:00 pm, beeblemonster
wrote:
It's not working at all....
would it have something to do with the fact that both files aren't on my
hardrive... they are on a company server in password protected folder?

I've been trying to get this to work for hours... with and without it open.

Try it with your table reference as $A:$P rather than $1:$65536. Not
sure why you have the HYPERLINK in A1, but you need the full path if
the file is not open (although you seem to have missed an apostrophe).

Hope this helps.

Pete

On Aug 14, 3:14*pm, beeblemonster
wrote:
I'ver tried it a couple of ways...

It says #NAME? when I dothis...

This is what C3's code looks like...
=VLOOKUP(A3,INDIRECT(A1),15)

and this is what A1's looks like...
=HYPERLINK('[file name]Sheet 1'!$1:$65536)

when I change A1 to \\folder\folder\folder\[file name]Sheet 1'!$1:$65536
it come with a #REF! error



"Pete_UK" wrote:
Post the formula(s) that you have tried, and tell us what error
message you are getting.

Pete

On Aug 13, 10:00 pm, beeblemonster
wrote:
It's not working at all....
would it have something to do with the fact that both files aren't on my
hardrive... they are on a company server in password protected folder?

I've been trying to get this to work for hours... with and without it open. - Hide quoted text -

- Show quoted text -

Sorry, I thought from the earlier posts that you would have downloaded
the Morefunc add-in and used INDIRECT.EXT. As you are trying to use
INDIRECT, this will fail if the other workbook is not open.

Pete

On Aug 14, 3:27*pm, Pete_UK wrote:
Try it with your table reference as $A:$P rather than $1:$65536. Not
sure why you have the HYPERLINK in A1, but you need the full path if
the file is not open (although you seem to have missed an apostrophe).

Hope this helps.

Pete

On Aug 14, 3:14*pm, beeblemonster



wrote:
I'ver tried it a couple of ways...

It says #NAME? when I dothis...

This is what C3's code looks like...
=VLOOKUP(A3,INDIRECT(A1),15)

and this is what A1's looks like...
=HYPERLINK('[file name]Sheet 1'!$1:$65536)

when I change A1 to \\folder\folder\folder\[file name]Sheet 1'!$1:$65536
it come with a #REF! error

"Pete_UK" wrote:
Post the formula(s) that you have tried, and tell us what error
message you are getting.

Pete

On Aug 13, 10:00 pm, beeblemonster
wrote:
It's not working at all....
would it have something to do with the fact that both files aren't on my
hardrive... they are on a company server in password protected folder?

I've been trying to get this to work for hours... with and without it open. - Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

It did the hyperlink automatically...
i can change it to just text...
I'll try the A-P thing, but where am I missing an apostrophe?

and I can't download morefunc, unless I get permission from an admin, so I
was going to make sure I could get it to work with the file in the backround
before I went through taht whole process. If I did use this program and
someone wanted to look at the file and change stuff on a different
computer... would they have to download the program also?

"Pete_UK" wrote:

Sorry, I thought from the earlier posts that you would have downloaded
the Morefunc add-in and used INDIRECT.EXT. As you are trying to use
INDIRECT, this will fail if the other workbook is not open.

Pete

On Aug 14, 3:27 pm, Pete_UK wrote:
Try it with your table reference as $A:$P rather than $1:$65536. Not
sure why you have the HYPERLINK in A1, but you need the full path if
the file is not open (although you seem to have missed an apostrophe).

Hope this helps.

Pete

On Aug 14, 3:14 pm, beeblemonster



wrote:
I'ver tried it a couple of ways...

It says #NAME? when I dothis...

This is what C3's code looks like...
=VLOOKUP(A3,INDIRECT(A1),15)

and this is what A1's looks like...
=HYPERLINK('[file name]Sheet 1'!$1:$65536)

when I change A1 to \\folder\folder\folder\[file name]Sheet 1'!$1:$65536
it come with a #REF! error

"Pete_UK" wrote:
Post the formula(s) that you have tried, and tell us what error
message you are getting.

Pete

On Aug 13, 10:00 pm, beeblemonster
wrote:
It's not working at all....
would it have something to do with the fact that both files aren't on my
hardrive... they are on a company server in password protected folder?

I've been trying to get this to work for hours... with and without it open. - Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -