INDIRECT.EXT Help Required

M

Mike McLellan

I want to use VLOOKUP and INDIRECT.EXT 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 have a call to VLOOKUP as follows:

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

When I try this, I get an #VALUE! error. I then tried the following:

=VLOOKUP(D10,INDIRECT.EXT("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"),3,FALSE)

This also results in an #VALUE! error.

If I change the call to =VLOOKUP(D10,INDIRECT(C10),3,FALSE) and open the
file then it works.

Can anyone tell me where the INDIRECT.EXT call is going wrong?
 
D

Dave Peterson

I'd try 4 double quotes first--but that's at the other thread.

Mike said:
I want to use VLOOKUP and INDIRECT.EXT 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 have a call to VLOOKUP as follows:

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

When I try this, I get an #VALUE! error. I then tried the following:

=VLOOKUP(D10,INDIRECT.EXT("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"),3,FALSE)

This also results in an #VALUE! error.

If I change the call to =VLOOKUP(D10,INDIRECT(C10),3,FALSE) and open the
file then it works.

Can anyone tell me where the INDIRECT.EXT call is going wrong?
Click to expand...
 
M

Mike McLellan

Tried 4 double quotes but no further forward - still get #VALUE! error

Dave Peterson said:
I'd try 4 double quotes first--but that's at the other thread.

Mike said:
I want to use VLOOKUP and INDIRECT.EXT 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 have a call to VLOOKUP as follows:

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

When I try this, I get an #VALUE! error. I then tried the following:

=VLOOKUP(D10,INDIRECT.EXT("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"),3,FALSE)

This also results in an #VALUE! error.

If I change the call to =VLOOKUP(D10,INDIRECT(C10),3,FALSE) and open the
file then it works.

Can anyone tell me where the INDIRECT.EXT call is going wrong?
Click to expand...
Click to expand...
 
D

Dave Peterson

Sorry.

Mike said:
Tried 4 double quotes but no further forward - still get #VALUE! error

Dave Peterson said:
I'd try 4 double quotes first--but that's at the other thread.

Mike said:
I want to use VLOOKUP and INDIRECT.EXT 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 have a call to VLOOKUP as follows:

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

When I try this, I get an #VALUE! error. I then tried the following:

=VLOOKUP(D10,INDIRECT.EXT("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"),3,FALSE)

This also results in an #VALUE! error.

If I change the call to =VLOOKUP(D10,INDIRECT(C10),3,FALSE) and open the
file then it works.

Can anyone tell me where the INDIRECT.EXT call is going wrong?
Click to expand...
Click to expand...
Click to expand...
 
M

Mike McLellan

Thanks for you help, Dave.

Anybody else got any ideas?

Dave Peterson said:
Sorry.

Mike said:
Tried 4 double quotes but no further forward - still get #VALUE! error

Dave Peterson said:
I'd try 4 double quotes first--but that's at the other thread.

Mike McLellan wrote:

I want to use VLOOKUP and INDIRECT.EXT 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 have a call to VLOOKUP as follows:

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

When I try this, I get an #VALUE! error. I then tried the following:

=VLOOKUP(D10,INDIRECT.EXT("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"),3,FALSE)

This also results in an #VALUE! error.

If I change the call to =VLOOKUP(D10,INDIRECT(C10),3,FALSE) and open the
file then it works.

Can anyone tell me where the INDIRECT.EXT call is going wrong?
Click to expand...
Click to expand...
Click to expand...
 
D

Dave Peterson

I thought I saw a forum at Laurent Longre's site.

If you don't get any other suggestions here, you may want to try there.

Mike said:
Thanks for you help, Dave.

Anybody else got any ideas?

Dave Peterson said:
Sorry.

Mike said:
Tried 4 double quotes but no further forward - still get #VALUE! error

:

I'd try 4 double quotes first--but that's at the other thread.

Mike McLellan wrote:

I want to use VLOOKUP and INDIRECT.EXT 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 have a call to VLOOKUP as follows:

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

When I try this, I get an #VALUE! error. I then tried the following:

=VLOOKUP(D10,INDIRECT.EXT("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"),3,FALSE)

This also results in an #VALUE! error.

If I change the call to =VLOOKUP(D10,INDIRECT(C10),3,FALSE) and open the
file then it works.

Can anyone tell me where the INDIRECT.EXT call is going wrong?
Click to expand...
Click to expand...
Click to expand...
 
D

Danny Lewis

I have EXACTLY the same problem. However I did read on one forum that you
cannot use INDIRECT.EXT as a 3rd dimension to VLOOKUP, so I gave up.

Dave Peterson said:
I thought I saw a forum at Laurent Longre's site.

If you don't get any other suggestions here, you may want to try there.

Mike said:
Thanks for you help, Dave.

Anybody else got any ideas?

Dave Peterson said:
Sorry.

Mike McLellan wrote:

Tried 4 double quotes but no further forward - still get #VALUE! error

:

I'd try 4 double quotes first--but that's at the other thread.

Mike McLellan wrote:

I want to use VLOOKUP and INDIRECT.EXT 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 have a call to VLOOKUP as follows:

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

When I try this, I get an #VALUE! error. I then tried the following:

=VLOOKUP(D10,INDIRECT.EXT("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"),3,FALSE)

This also results in an #VALUE! error.

If I change the call to =VLOOKUP(D10,INDIRECT(C10),3,FALSE) and open the
file then it works.

Can anyone tell me where the INDIRECT.EXT call is 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

VLOOKUP 7
INDIRECT.EXT 2
Losing formula references to other sheets - HELP! 0
help - VBA 1
Help with a formula 1
'INDIRECT'ly referred cell's own simple formula fails.... 3
My Macro crashes excel - anyone know why. 0
Help with VLOOKUP 6

Top