INDIRECT.EXT Help Required

Mike McLellan · 05-03-2006, 11:40 AM

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?

Dave Peterson · 05-03-2006, 02:10 PM

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?

--

Dave Peterson

Mike McLellan · 05-03-2006, 03:00 PM

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

"Dave Peterson" wrote:

> 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?
>
> --
>
> Dave Peterson
>

Dave Peterson · 05-03-2006, 03:10 PM

Sorry.

Mike McLellan wrote:
>
> Tried 4 double quotes but no further forward - still get #VALUE! error
>
> "Dave Peterson" wrote:
>
> > 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?
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

Mike McLellan · 05-04-2006, 02:25 AM

Thanks for you help, Dave.

Anybody else got any ideas?

"Dave Peterson" wrote:

> Sorry.
>
> Mike McLellan wrote:
> >
> > Tried 4 double quotes but no further forward - still get #VALUE! error
> >
> > "Dave Peterson" wrote:
> >
> > > 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?
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

Dave Peterson · 05-04-2006, 09:00 AM

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 McLellan wrote:
>
> Thanks for you help, Dave.
>
> Anybody else got any ideas?
>
> "Dave Peterson" wrote:
>
> > Sorry.
> >
> > Mike McLellan wrote:
> > >
> > > Tried 4 double quotes but no further forward - still get #VALUE! error
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > 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?
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

Danny Lewis · 07-19-2006, 09:05 AM

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" wrote:

> 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 McLellan wrote:
> >
> > Thanks for you help, Dave.
> >
> > Anybody else got any ideas?
> >
> > "Dave Peterson" wrote:
> >
> > > Sorry.
> > >
> > > Mike McLellan wrote:
> > > >
> > > > Tried 4 double quotes but no further forward - still get #VALUE! error
> > > >
> > > > "Dave Peterson" wrote:
> > > >
> > > > > 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?
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>