software

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
quot;'[d:\....\file.xls]weekly'!$A$1:$Z$100quot; 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

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

xcell05.free.fr/english/index...func_Functions

quot;Mike McLellanquot; wrote:

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

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?

quot;Barb Reinhardtquot; wrote:

gt; You'll get #N/A if file.xls is not open. If you want to access data within
gt; a closed file, you can use the indirect.ext function within the addin Morefunc
gt;
gt; xcell05.free.fr/english/index...func_Functions
gt;
gt; quot;Mike McLellanquot; wrote:
gt;
gt; gt; I want to be able to automatically copy data from a number of different
gt; gt; spreadsheets to a summary spreadsheet on a weekly basis using the VLOOKUP
gt; gt; function. The second parameter to the function, which looks something like
gt; gt; quot;'[d:\....\file.xls]weekly'!$A$1:$Z$100quot; has been set up in another cell
gt; gt; within the summary spreadsheet using the CONCATENATE function. How can I
gt; gt; make sure that when I reference this cell within the VLOOKUP call it's
gt; gt; contents are evaluated so that the function correctly refers to the range
gt; gt; within the external file? Currently I am getting a #N/A error

Is the Add-in available to use.

Tools-gt; Add-ins ... check Morefunc

quot;Mike McLellanquot; wrote:

gt; Thanks Barb.
gt;
gt; I tried this - the call now looks like
gt; =VLOOKUP(D10,INDIRECT.EXT($C10),3,FALSE) - and I get the error #VALUE!. The
gt; contents of cell C10 display as
gt; '[D:\Documents and Settings\fzvtc6\My Documents\EDS
gt; Documents\Shared\Excel\LPS\SLA\20060320\20060320 - Weekly - All locations -
gt; LPS - Live.xls]Weekly'!$A$1:$Z:50
gt;
gt; Any idea where I'm going wrong?
gt;
gt; quot;Barb Reinhardtquot; wrote:
gt;
gt; gt; You'll get #N/A if file.xls is not open. If you want to access data within
gt; gt; a closed file, you can use the indirect.ext function within the addin Morefunc
gt; gt;
gt; gt; xcell05.free.fr/english/index...func_Functions
gt; gt;
gt; gt; quot;Mike McLellanquot; wrote:
gt; gt;
gt; gt; gt; I want to be able to automatically copy data from a number of different
gt; gt; gt; spreadsheets to a summary spreadsheet on a weekly basis using the VLOOKUP
gt; gt; gt; function. The second parameter to the function, which looks something like
gt; gt; gt; quot;'[d:\....\file.xls]weekly'!$A$1:$Z$100quot; has been set up in another cell
gt; gt; gt; within the summary spreadsheet using the CONCATENATE function. How can I
gt; gt; gt; make sure that when I reference this cell within the VLOOKUP call it's
gt; gt; gt; contents are evaluated so that the function correctly refers to the range
gt; gt; gt; within the external file? Currently I am getting a #N/A error

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?

quot;Mike McLellanquot; wrote:

gt; Thanks Barb.
gt;
gt; I tried this - the call now looks like
gt; =VLOOKUP(D10,INDIRECT.EXT($C10),3,FALSE) - and I get the error #VALUE!. The
gt; contents of cell C10 display as
gt; '[D:\Documents and Settings\fzvtc6\My Documents\EDS
gt; Documents\Shared\Excel\LPS\SLA\20060320\20060320 - Weekly - All locations -
gt; LPS - Live.xls]Weekly'!$A$1:$Z:50
gt;
gt; Any idea where I'm going wrong?
gt;
gt; quot;Barb Reinhardtquot; wrote:
gt;
gt; gt; You'll get #N/A if file.xls is not open. If you want to access data within
gt; gt; a closed file, you can use the indirect.ext function within the addin Morefunc
gt; gt;
gt; gt; xcell05.free.fr/english/index...func_Functions
gt; gt;
gt; gt; quot;Mike McLellanquot; wrote:
gt; gt;
gt; gt; gt; I want to be able to automatically copy data from a number of different
gt; gt; gt; spreadsheets to a summary spreadsheet on a weekly basis using the VLOOKUP
gt; gt; gt; function. The second parameter to the function, which looks something like
gt; gt; gt; quot;'[d:\....\file.xls]weekly'!$A$1:$Z$100quot; has been set up in another cell
gt; gt; gt; within the summary spreadsheet using the CONCATENATE function. How can I
gt; gt; gt; make sure that when I reference this cell within the VLOOKUP call it's
gt; gt; gt; contents are evaluated so that the function correctly refers to the range
gt; gt; gt; within the external file? Currently I am getting a #N/A error

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!

quot;Barb Reinhardtquot; wrote:

gt; Also it seems as though I've had issues with the beginning '. I *think* I
gt; had to add an extra one to get it to work. Don't remember why. Did you
gt; just try to put the value that's in C10 in the vlookup to see if that works?
gt;
gt; quot;Mike McLellanquot; wrote:
gt;
gt; gt; Thanks Barb.
gt; gt;
gt; gt; I tried this - the call now looks like
gt; gt; =VLOOKUP(D10,INDIRECT.EXT($C10),3,FALSE) - and I get the error #VALUE!. The
gt; gt; contents of cell C10 display as
gt; gt; '[D:\Documents and Settings\fzvtc6\My Documents\EDS
gt; gt; Documents\Shared\Excel\LPS\SLA\20060320\20060320 - Weekly - All locations -
gt; gt; LPS - Live.xls]Weekly'!$A$1:$Z:50
gt; gt;
gt; gt; Any idea where I'm going wrong?
gt; gt;
gt; gt; quot;Barb Reinhardtquot; wrote:
gt; gt;
gt; gt; gt; You'll get #N/A if file.xls is not open. If you want to access data within
gt; gt; gt; a closed file, you can use the indirect.ext function within the addin Morefunc
gt; gt; gt;
gt; gt; gt; xcell05.free.fr/english/index...func_Functions
gt; gt; gt;
gt; gt; gt; quot;Mike McLellanquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; I want to be able to automatically copy data from a number of different
gt; gt; gt; gt; spreadsheets to a summary spreadsheet on a weekly basis using the VLOOKUP
gt; gt; gt; gt; function. The second parameter to the function, which looks something like
gt; gt; gt; gt; quot;'[d:\....\file.xls]weekly'!$A$1:$Z$100quot; has been set up in another cell
gt; gt; gt; gt; within the summary spreadsheet using the CONCATENATE function. How can I
gt; gt; gt; gt; make sure that when I reference this cell within the VLOOKUP call it's
gt; gt; gt; gt; contents are evaluated so that the function correctly refers to the range
gt; gt; gt; gt; within the external file? Currently I am getting a #N/A error

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$

quot;Mike McLellanquot; wrote:

gt; Barb,
gt;
gt; The add-in is available to use.
gt;
gt; When I type in the contents of C10 (as in previous posting) I get a #N/A
gt; error and when I go back to look at the formula, it has been corrupted alomng
gt; the following lines
gt;
gt; =VLOOKUP(D10,'[D:\Documents and Settings\fzvtc6\My Documents\EDS
gt; Documents\Shared\Excel\LPS\SLA\20060320\[20060320 - Weekly - All locations -
gt; LPS - Live.xls]Weekly]20060320 - Weekly - All locatio'!$A$1:$Z50,3,FALSE)
gt;
gt; I'm beginning to lose the plot!
gt;
gt; quot;Barb Reinhardtquot; wrote:
gt;
gt; gt; Also it seems as though I've had issues with the beginning '. I *think* I
gt; gt; had to add an extra one to get it to work. Don't remember why. Did you
gt; gt; just try to put the value that's in C10 in the vlookup to see if that works?
gt; gt;
gt; gt; quot;Mike McLellanquot; wrote:
gt; gt;
gt; gt; gt; Thanks Barb.
gt; gt; gt;
gt; gt; gt; I tried this - the call now looks like
gt; gt; gt; =VLOOKUP(D10,INDIRECT.EXT($C10),3,FALSE) - and I get the error #VALUE!. The
gt; gt; gt; contents of cell C10 display as
gt; gt; gt; '[D:\Documents and Settings\fzvtc6\My Documents\EDS
gt; gt; gt; Documents\Shared\Excel\LPS\SLA\20060320\20060320 - Weekly - All locations -
gt; gt; gt; LPS - Live.xls]Weekly'!$A$1:$Z:50
gt; gt; gt;
gt; gt; gt; Any idea where I'm going wrong?
gt; gt; gt;
gt; gt; gt; quot;Barb Reinhardtquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; You'll get #N/A if file.xls is not open. If you want to access data within
gt; gt; gt; gt; a closed file, you can use the indirect.ext function within the addin Morefunc
gt; gt; gt; gt;
gt; gt; gt; gt; xcell05.free.fr/english/index...func_Functions
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Mike McLellanquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; I want to be able to automatically copy data from a number of different
gt; gt; gt; gt; gt; spreadsheets to a summary spreadsheet on a weekly basis using the VLOOKUP
gt; gt; gt; gt; gt; function. The second parameter to the function, which looks something like
gt; gt; gt; gt; gt; quot;'[d:\....\file.xls]weekly'!$A$1:$Z$100quot; has been set up in another cell
gt; gt; gt; gt; gt; within the summary spreadsheet using the CONCATENATE function. How can I
gt; gt; gt; gt; gt; make sure that when I reference this cell within the VLOOKUP call it's
gt; gt; gt; gt; gt; contents are evaluated so that the function correctly refers to the range
gt; gt; gt; gt; gt; within the external file? Currently I am getting a #N/A error