VLOOKUP

M

Mike McLellan

I want to use VLOOKUP to help produce a summary spreadsheet from a number of
files.

Cell C10 contains - =CONCATENATE("D:\Documents and Settings\fzvtc6\My
Documents\EDS
Documents\Shared\Excel\LPS\SLA\",TEXT($A10,"yyyymmdd"),"\[",TEXT($A10,"yyyymmdd")," - Weekly - All locations - LPS - Live.xls]Weekly!$A$1:$Z:50")

which evaluates 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

In cell F10, I want to be able to incorporate a call to VLOOKUP along the
following lines - =VLOOKUP(D10,INDIRECT(C10),3,FALSE)

When I try this, I get an #REF! error. Can anyone tell me where I'm going
wrong?
 
D

Dave Peterson

If you open that "sending" workbook, you'll see that you did everything perfect.

But =indirect() only works if the "sending" workbook is open.

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may help you.

Mike said:
I want to use VLOOKUP to help produce a summary spreadsheet from a number of
files.

Cell C10 contains - =CONCATENATE("D:\Documents and Settings\fzvtc6\My
Documents\EDS
Documents\Shared\Excel\LPS\SLA\",TEXT($A10,"yyyymmdd"),"\[",TEXT($A10,"yyyymmdd")," - Weekly - All locations - LPS - Live.xls]Weekly!$A$1:$Z:50")

which evaluates 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

In cell F10, I want to be able to incorporate a call to VLOOKUP along the
following lines - =VLOOKUP(D10,INDIRECT(C10),3,FALSE)

When I try this, I get an #REF! error. Can anyone tell me where I'm going
wrong?
Click to expand...
 
M

Mike McLellan

Thanks, Dave.

INDIRECT.EXT requires that the whole string is enclosed by double quotes -
how do I amend formula in C10 to achieve this?

Dave Peterson said:
If you open that "sending" workbook, you'll see that you did everything perfect.

But =indirect() only works if the "sending" workbook is open.

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may help you.

Mike said:
I want to use VLOOKUP to help produce a summary spreadsheet from a number of
files.

Cell C10 contains - =CONCATENATE("D:\Documents and Settings\fzvtc6\My
Documents\EDS
Documents\Shared\Excel\LPS\SLA\",TEXT($A10,"yyyymmdd"),"\[",TEXT($A10,"yyyymmdd")," - Weekly - All locations - LPS - Live.xls]Weekly!$A$1:$Z:50")

which evaluates 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

In cell F10, I want to be able to incorporate a call to VLOOKUP along the
following lines - =VLOOKUP(D10,INDIRECT(C10),3,FALSE)

When I try this, I get an #REF! error. Can anyone tell me where I'm going
wrong?
Click to expand...
Click to expand...
 
D

Dave Peterson

Maybe...

=VLOOKUP(D10,INDIRECT.ext(""""&C10&""""),3,FALSE)



Mike said:
Thanks, Dave.

INDIRECT.EXT requires that the whole string is enclosed by double quotes -
how do I amend formula in C10 to achieve this?

Dave Peterson said:
If you open that "sending" workbook, you'll see that you did everything perfect.

But =indirect() only works if the "sending" workbook is open.

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may help you.

Mike said:
I want to use VLOOKUP to help produce a summary spreadsheet from a number of
files.

Cell C10 contains - =CONCATENATE("D:\Documents and Settings\fzvtc6\My
Documents\EDS
Documents\Shared\Excel\LPS\SLA\",TEXT($A10,"yyyymmdd"),"\[",TEXT($A10,"yyyymmdd")," - Weekly - All locations - LPS - Live.xls]Weekly!$A$1:$Z:50")

which evaluates 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

In cell F10, I want to be able to incorporate a call to VLOOKUP along the
following lines - =VLOOKUP(D10,INDIRECT(C10),3,FALSE)

When I try this, I get an #REF! error. Can anyone tell me where I'm going
wrong?
Click to expand...
Click to expand...
Click to expand...
 
M

Mike McLellan

Almost there.

I am now getting an #VALUE! error - any ideas?

Dave Peterson said:
Maybe...

=VLOOKUP(D10,INDIRECT.ext(""""&C10&""""),3,FALSE)



Mike said:
Thanks, Dave.

INDIRECT.EXT requires that the whole string is enclosed by double quotes -
how do I amend formula in C10 to achieve this?

Dave Peterson said:
If you open that "sending" workbook, you'll see that you did everything perfect.

But =indirect() only works if the "sending" workbook is open.

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may help you.

Mike McLellan wrote:

I want to use VLOOKUP to help produce a summary spreadsheet from a number of
files.

Cell C10 contains - =CONCATENATE("D:\Documents and Settings\fzvtc6\My
Documents\EDS
Documents\Shared\Excel\LPS\SLA\",TEXT($A10,"yyyymmdd"),"\[",TEXT($A10,"yyyymmdd")," - Weekly - All locations - LPS - Live.xls]Weekly!$A$1:$Z:50")

which evaluates 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

In cell F10, I want to be able to incorporate a call to VLOOKUP along the
following lines - =VLOOKUP(D10,INDIRECT(C10),3,FALSE)

When I try this, I get an #REF! error. Can anyone tell me where I'm going
wrong?
Click to expand...
Click to expand...
Click to expand...
 
D

Dave Peterson

I'd open that other workbook.
Build my formula so that it works with =indirect()
close the other workbook (so you see the path)
and see if just changing =indirect() to =indirect.ext() would make it work.

Mike said:
Almost there.

I am now getting an #VALUE! error - any ideas?

Dave Peterson said:
Maybe...

=VLOOKUP(D10,INDIRECT.ext(""""&C10&""""),3,FALSE)



Mike said:
Thanks, Dave.

INDIRECT.EXT requires that the whole string is enclosed by double quotes -
how do I amend formula in C10 to achieve this?

:

If you open that "sending" workbook, you'll see that you did everything perfect.

But =indirect() only works if the "sending" workbook is open.

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may help you.

Mike McLellan wrote:

I want to use VLOOKUP to help produce a summary spreadsheet from a number of
files.

Cell C10 contains - =CONCATENATE("D:\Documents and Settings\fzvtc6\My
Documents\EDS
Documents\Shared\Excel\LPS\SLA\",TEXT($A10,"yyyymmdd"),"\[",TEXT($A10,"yyyymmdd")," - Weekly - All locations - LPS - Live.xls]Weekly!$A$1:$Z:50")

which evaluates 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

In cell F10, I want to be able to incorporate a call to VLOOKUP along the
following lines - =VLOOKUP(D10,INDIRECT(C10),3,FALSE)

When I try this, I get an #REF! error. Can anyone tell me where I'm going
wrong?
Click to expand...
Click to expand...
Click to expand...
 
M

Mike McLellan

Did as you suggested:

- =VLOOKUP(D11,INDIRECT(C11),3,FALSE) works
- changed to =VLOOKUP(D11,INDIRECT.ext(C11),3,FALSE) - doesn't work
- tried =VLOOKUP(D11,INDIRECT("""&C11&"""),3,FALSE) - doesn't work

Any ideas?

Dave Peterson said:
I'd open that other workbook.
Build my formula so that it works with =indirect()
close the other workbook (so you see the path)
and see if just changing =indirect() to =indirect.ext() would make it work.

Mike said:
Almost there.

I am now getting an #VALUE! error - any ideas?

Dave Peterson said:
Maybe...

=VLOOKUP(D10,INDIRECT.ext(""""&C10&""""),3,FALSE)



Mike McLellan wrote:

Thanks, Dave.

INDIRECT.EXT requires that the whole string is enclosed by double quotes -
how do I amend formula in C10 to achieve this?

:

If you open that "sending" workbook, you'll see that you did everything perfect.

But =indirect() only works if the "sending" workbook is open.

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may help you.

Mike McLellan wrote:

I want to use VLOOKUP to help produce a summary spreadsheet from a number of
files.

Cell C10 contains - =CONCATENATE("D:\Documents and Settings\fzvtc6\My
Documents\EDS
Documents\Shared\Excel\LPS\SLA\",TEXT($A10,"yyyymmdd"),"\[",TEXT($A10,"yyyymmdd")," - Weekly - All locations - LPS - Live.xls]Weekly!$A$1:$Z:50")

which evaluates 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

In cell F10, I want to be able to incorporate a call to VLOOKUP along the
following lines - =VLOOKUP(D10,INDIRECT(C10),3,FALSE)

When I try this, I get an #REF! error. Can anyone tell me where I'm going
wrong?
Click to expand...
Click to expand...
Click to expand...
 
D

Dave Peterson

I used 4 double quotes in my first suggestion:
=VLOOKUP(D10,INDIRECT.ext(""""&C10&""""),3,FALSE)

Did that not work?

Mike said:
Did as you suggested:

- =VLOOKUP(D11,INDIRECT(C11),3,FALSE) works
- changed to =VLOOKUP(D11,INDIRECT.ext(C11),3,FALSE) - doesn't work
- tried =VLOOKUP(D11,INDIRECT("""&C11&"""),3,FALSE) - doesn't work

Any ideas?

Dave Peterson said:
I'd open that other workbook.
Build my formula so that it works with =indirect()
close the other workbook (so you see the path)
and see if just changing =indirect() to =indirect.ext() would make it work.

Mike said:
Almost there.

I am now getting an #VALUE! error - any ideas?

:

Maybe...

=VLOOKUP(D10,INDIRECT.ext(""""&C10&""""),3,FALSE)



Mike McLellan wrote:

Thanks, Dave.

INDIRECT.EXT requires that the whole string is enclosed by double quotes -
how do I amend formula in C10 to achieve this?

:

If you open that "sending" workbook, you'll see that you did everything perfect.

But =indirect() only works if the "sending" workbook is open.

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may help you.

Mike McLellan wrote:

I want to use VLOOKUP to help produce a summary spreadsheet from a number of
files.

Cell C10 contains - =CONCATENATE("D:\Documents and Settings\fzvtc6\My
Documents\EDS
Documents\Shared\Excel\LPS\SLA\",TEXT($A10,"yyyymmdd"),"\[",TEXT($A10,"yyyymmdd")," - Weekly - All locations - LPS - Live.xls]Weekly!$A$1:$Z:50")

which evaluates 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

In cell F10, I want to be able to incorporate a call to VLOOKUP along the
following lines - =VLOOKUP(D10,INDIRECT(C10),3,FALSE)

When I try this, I get an #REF! error. Can anyone tell me where I'm going
wrong?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

INDIRECT.EXT Help Required 6
help - VBA 1
VLOOKUP 6
Return an absolutely empty cell ... but not "" 3
INDEX, VLOOKUP? What's best for this situation? 7
Vlookup file location changes depending on value within cell 1
Vlookup ref! 2
Vlookup problem 5

Top