VLOOKUP

[Mike McLellan]

I want to be able to automatically copy data from a number of different
spreadsheets to a summary spreadsheet on a weekly basis using the VLOOKUP
function. The second parameter to the function, which looks something like
"'[d:\....\file.xls]weekly'!$A$1:$Z$100" has been set up in another cell
within the summary spreadsheet using the CONCATENATE function. How can I
make sure that when I reference this cell within the VLOOKUP call it's
contents are evaluated so that the function correctly refers to the range
within the external file? Currently I am getting a #N/A error

 

[Barb Reinhardt]

You'll get #N/A if file.xls is not open. If you want to access data within
a closed file, you can use the indirect.ext function within the addin Morefunc

http://xcell05.free.fr/english/index.html#Morefunc_Functions

 

[Mike McLellan]

Thanks Barb.

I tried this - the call now looks like
=VLOOKUP(D10,INDIRECT.EXT($C10),3,FALSE) - and I get the error #VALUE!. The
contents of cell C10 display as
'[D:\Documents and Settings\fzvtc6\My Documents\EDS
Documents\Shared\Excel\LPS\SLA\20060320\20060320 - Weekly - All locations -
LPS - Live.xls]Weekly'!$A$1:$Z:50

Any idea where I'm going wrong?

You'll get #N/A if file.xls is not open. If you want to access data within
a closed file, you can use the indirect.ext function within the addin Morefunc

http://xcell05.free.fr/english/index.html#Morefunc_Functions

I want to be able to automatically copy data from a number of different
spreadsheets to a summary spreadsheet on a weekly basis using the VLOOKUP
function. The second parameter to the function, which looks something like
"'[d:\....\file.xls]weekly'!$A$1:$Z$100" has been set up in another cell
within the summary spreadsheet using the CONCATENATE function. How can I
make sure that when I reference this cell within the VLOOKUP call it's
contents are evaluated so that the function correctly refers to the range
within the external file? Currently I am getting a #N/A error

 

[Barb Reinhardt]

Is the Add-in available to use.

Tools-> Add-ins ... check Morefunc

Thanks Barb.

I tried this - the call now looks like
=VLOOKUP(D10,INDIRECT.EXT($C10),3,FALSE) - and I get the error #VALUE!. The
contents of cell C10 display as
'[D:\Documents and Settings\fzvtc6\My Documents\EDS
Documents\Shared\Excel\LPS\SLA\20060320\20060320 - Weekly - All locations -
LPS - Live.xls]Weekly'!$A$1:$Z:50

Any idea where I'm going wrong?

You'll get #N/A if file.xls is not open. If you want to access data within
a closed file, you can use the indirect.ext function within the addin Morefunc

http://xcell05.free.fr/english/index.html#Morefunc_Functions

I want to be able to automatically copy data from a number of different
spreadsheets to a summary spreadsheet on a weekly basis using the VLOOKUP
function. The second parameter to the function, which looks something like
"'[d:\....\file.xls]weekly'!$A$1:$Z$100" has been set up in another cell
within the summary spreadsheet using the CONCATENATE function. How can I
make sure that when I reference this cell within the VLOOKUP call it's
contents are evaluated so that the function correctly refers to the range
within the external file? Currently I am getting a #N/A error

 

[Barb Reinhardt]

Also it seems as though I've had issues with the beginning '. I *think* I
had to add an extra one to get it to work. Don't remember why. Did you
just try to put the value that's in C10 in the vlookup to see if that works?

Thanks Barb.

I tried this - the call now looks like
=VLOOKUP(D10,INDIRECT.EXT($C10),3,FALSE) - and I get the error #VALUE!. The
contents of cell C10 display as
'[D:\Documents and Settings\fzvtc6\My Documents\EDS
Documents\Shared\Excel\LPS\SLA\20060320\20060320 - Weekly - All locations -
LPS - Live.xls]Weekly'!$A$1:$Z:50

Any idea where I'm going wrong?

You'll get #N/A if file.xls is not open. If you want to access data within
a closed file, you can use the indirect.ext function within the addin Morefunc

http://xcell05.free.fr/english/index.html#Morefunc_Functions

I want to be able to automatically copy data from a number of different
spreadsheets to a summary spreadsheet on a weekly basis using the VLOOKUP
function. The second parameter to the function, which looks something like
"'[d:\....\file.xls]weekly'!$A$1:$Z$100" has been set up in another cell
within the summary spreadsheet using the CONCATENATE function. How can I
make sure that when I reference this cell within the VLOOKUP call it's
contents are evaluated so that the function correctly refers to the range
within the external file? Currently I am getting a #N/A error

 

[Mike McLellan]

Barb,

The add-in is available to use.

When I type in the contents of C10 (as in previous posting) I get a #N/A
error and when I go back to look at the formula, it has been corrupted alomng
the following lines

=VLOOKUP(D10,'[D:\Documents and Settings\fzvtc6\My Documents\EDS
Documents\Shared\Excel\LPS\SLA\20060320\[20060320 - Weekly - All locations -
LPS - Live.xls]Weekly]20060320 - Weekly - All locatio'!$A$1:$Z50,3,FALSE)

I'm beginning to lose the plot!

Also it seems as though I've had issues with the beginning '. I *think* I
had to add an extra one to get it to work. Don't remember why. Did you
just try to put the value that's in C10 in the vlookup to see if that works?

Thanks Barb.

I tried this - the call now looks like
=VLOOKUP(D10,INDIRECT.EXT($C10),3,FALSE) - and I get the error #VALUE!. The
contents of cell C10 display as
'[D:\Documents and Settings\fzvtc6\My Documents\EDS
Documents\Shared\Excel\LPS\SLA\20060320\20060320 - Weekly - All locations -
LPS - Live.xls]Weekly'!$A$1:$Z:50

Any idea where I'm going wrong?

You'll get #N/A if file.xls is not open. If you want to access data within
a closed file, you can use the indirect.ext function within the addin Morefunc

http://xcell05.free.fr/english/index.html#Morefunc_Functions

:

I want to be able to automatically copy data from a number of different
spreadsheets to a summary spreadsheet on a weekly basis using the VLOOKUP
function. The second parameter to the function, which looks something like
"'[d:\....\file.xls]weekly'!$A$1:$Z$100" has been set up in another cell
within the summary spreadsheet using the CONCATENATE function. How can I
make sure that when I reference this cell within the VLOOKUP call it's
contents are evaluated so that the function correctly refers to the range
within the external file? Currently I am getting a #N/A error

 

[Barb Reinhardt]

Without counting the characters, I'm wondering if you've hit a character
count of 255 for cell D. You *may* need to map another network drive to get
what you want (assuming you use the same location every time). Could you map
D:\Documents and Settings\fzvtc6\My Documents\EDS
Documents\Shared\Excel\LPS\SLA

to another drive? (say Q
In that case, your value in D10 would be
Q:\20060320\20060320 - Weekly - All locations -
LPS - Live.xls]Weekly'!$A$1:$Z:50

If you copied this in, you need to change Z: to Z$

Barb,

The add-in is available to use.

When I type in the contents of C10 (as in previous posting) I get a #N/A
error and when I go back to look at the formula, it has been corrupted alomng
the following lines

=VLOOKUP(D10,'[D:\Documents and Settings\fzvtc6\My Documents\EDS
Documents\Shared\Excel\LPS\SLA\20060320\[20060320 - Weekly - All locations -
LPS - Live.xls]Weekly]20060320 - Weekly - All locatio'!$A$1:$Z50,3,FALSE)

I'm beginning to lose the plot!

Also it seems as though I've had issues with the beginning '. I *think* I
had to add an extra one to get it to work. Don't remember why. Did you
just try to put the value that's in C10 in the vlookup to see if that works?

Thanks Barb.

I tried this - the call now looks like
=VLOOKUP(D10,INDIRECT.EXT($C10),3,FALSE) - and I get the error #VALUE!. The
contents of cell C10 display as
'[D:\Documents and Settings\fzvtc6\My Documents\EDS
Documents\Shared\Excel\LPS\SLA\20060320\20060320 - Weekly - All locations -
LPS - Live.xls]Weekly'!$A$1:$Z:50

Any idea where I'm going wrong?

:

You'll get #N/A if file.xls is not open. If you want to access data within
a closed file, you can use the indirect.ext function within the addin Morefunc

http://xcell05.free.fr/english/index.html#Morefunc_Functions

:

I want to be able to automatically copy data from a number of different
spreadsheets to a summary spreadsheet on a weekly basis using the VLOOKUP
function. The second parameter to the function, which looks something like
"'[d:\....\file.xls]weekly'!$A$1:$Z$100" has been set up in another cell
within the summary spreadsheet using the CONCATENATE function. How can I
make sure that when I reference this cell within the VLOOKUP call it's
contents are evaluated so that the function correctly refers to the range
within the external file? Currently I am getting a #N/A error