Use INDIRECT function to reference a value in closed file

Thanks to all (Bob, Ron and Andy) for the information.

"Andy Wiggins" wrote:

> This file might be a help:
> http://www.bygsoftware.com/examples/...SqlRequest.zip
> It's in the "Excel with Access Databases" section on page:
> http://www.bygsoftware.com/examples/examples.htm
>
> This workbook demonstrates how to get data direct from an MS Access table,
> or from an open or closed MS Excel workbook using the workbook function
> SQL.REQUEST.
>
> Recently updated to show the use of SQL.REQUEST in the same workbook.
>
> The code is open and commented.
>
>
> --
> Andy Wiggins FCCA
> www.BygSoftware.com
> Excel, Access and VBA Consultancy
> -
>
> "Saravan" <[email protected]> wrote in message
> news:[email protected]...
> >
> > Book1.xls - Cell C3 =
> > CONCATENATE("'",E:\Saravan\Jun2005,"\[Book2.xls]Sheet1'!$A$4:$B$18")
> > Book1.xls - Cell A4:A13 have dates (01-jun-2005 to 10-jun-2005)
> > Book1.xls - Cell B4:B13 should lookup a value corresponding to the date in
> > Book2.xls
> >
> > I have tried "=VLOOKUP(A4,INDIRECT(C3),2,FALSE)", Seems to work only with
> > open work books. Is there any function that could be used on closed
> workbooks.
> >
> > Saravan
> >
> >
>
>
>

INDIRECT only works with open workbooks. You could try Harlan Grove's Pull
function, which you can get at ftp://members.aol.com/hrlngrv/pull.zip

--
HTH

Bob Phillips

"Saravan" <[email protected]> wrote in message
news:[email protected]...
>
> Book1.xls - Cell C3 =
> CONCATENATE("'",E:\Saravan\Jun2005,"\[Book2.xls]Sheet1'!$A$4:$B$18")
> Book1.xls - Cell A4:A13 have dates (01-jun-2005 to 10-jun-2005)
> Book1.xls - Cell B4:B13 should lookup a value corresponding to the date in
> Book2.xls
>
> I have tried "=VLOOKUP(A4,INDIRECT(C3),2,FALSE)", Seems to work only with
> open work books. Is there any function that could be used on closed
workbooks.
>
> Saravan
>
>

Is there a function to replace INDIRECT in microsoft excel to be used in
vlookup.

"Bob Phillips" wrote:

> INDIRECT only works with open workbooks. You could try Harlan Grove's Pull
> function, which you can get at ftp://members.aol.com/hrlngrv/pull.zip
>
> --
> HTH
>
> Bob Phillips
>
> "Saravan" <[email protected]> wrote in message
> news:[email protected]...
> >
> > Book1.xls - Cell C3 =
> > CONCATENATE("'",E:\Saravan\Jun2005,"\[Book2.xls]Sheet1'!$A$4:$B$18")
> > Book1.xls - Cell A4:A13 have dates (01-jun-2005 to 10-jun-2005)
> > Book1.xls - Cell B4:B13 should lookup a value corresponding to the date in
> > Book2.xls
> >
> > I have tried "=VLOOKUP(A4,INDIRECT(C3),2,FALSE)", Seems to work only with
> > open work books. Is there any function that could be used on closed
> workbooks.
> >
> > Saravan
> >
> >
>
>
>

On Tue, 28 Jun 2005 02:40:03 -0700, "Saravan"
<[email protected]> wrote:

>
>Book1.xls - Cell C3 =
>CONCATENATE("'",E:\Saravan\Jun2005,"\[Book2.xls]Sheet1'!$A$4:$B$18")
>Book1.xls - Cell A4:A13 have dates (01-jun-2005 to 10-jun-2005)
>Book1.xls - Cell B4:B13 should lookup a value corresponding to the date in
>Book2.xls
>
>I have tried "=VLOOKUP(A4,INDIRECT(C3),2,FALSE)", Seems to work only with
>open work books. Is there any function that could be used on closed workbooks.
>
>Saravan
>

Go to http://xcell05.free.fr/english/.

and download and install Longre's free morefunc.xll add-in. Then use the
INDIRECT.EXT function.


--ron

No, that is why I suggested Harlan's UDF.

--
HTH

Bob Phillips

"Saravan" <[email protected]> wrote in message
news:[email protected]...
>
> Is there a function to replace INDIRECT in microsoft excel to be used in
> vlookup.
>
> "Bob Phillips" wrote:
>
> > INDIRECT only works with open workbooks. You could try Harlan Grove's
Pull
> > function, which you can get at ftp://members.aol.com/hrlngrv/pull.zip
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Saravan" <[email protected]> wrote in message
> > news:[email protected]...
> > >
> > > Book1.xls - Cell C3 =
> > > CONCATENATE("'",E:\Saravan\Jun2005,"\[Book2.xls]Sheet1'!$A$4:$B$18")
> > > Book1.xls - Cell A4:A13 have dates (01-jun-2005 to 10-jun-2005)
> > > Book1.xls - Cell B4:B13 should lookup a value corresponding to the
date in
> > > Book2.xls
> > >
> > > I have tried "=VLOOKUP(A4,INDIRECT(C3),2,FALSE)", Seems to work only
with
> > > open work books. Is there any function that could be used on closed
> > workbooks.
> > >
> > > Saravan
> > >
> > >
> >
> >
> >

This file might be a help:
http://www.bygsoftware.com/examples/...SqlRequest.zip
It's in the "Excel with Access Databases" section on page:
http://www.bygsoftware.com/examples/examples.htm

This workbook demonstrates how to get data direct from an MS Access table,
or from an open or closed MS Excel workbook using the workbook function
SQL.REQUEST.

Recently updated to show the use of SQL.REQUEST in the same workbook.

The code is open and commented.


--
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy
-

"Saravan" <[email protected]> wrote in message
news:[email protected]...
>
> Book1.xls - Cell C3 =
> CONCATENATE("'",E:\Saravan\Jun2005,"\[Book2.xls]Sheet1'!$A$4:$B$18")
> Book1.xls - Cell A4:A13 have dates (01-jun-2005 to 10-jun-2005)
> Book1.xls - Cell B4:B13 should lookup a value corresponding to the date in
> Book2.xls
>
> I have tried "=VLOOKUP(A4,INDIRECT(C3),2,FALSE)", Seems to work only with
> open work books. Is there any function that could be used on closed
workbooks.
>
> Saravan
>
>

INDIRECT only works with open workbooks. You could try Harlan Grove's Pull
function, which you can get at ftp://members.aol.com/hrlngrv/pull.zip

--
HTH

Bob Phillips

"Saravan" <[email protected]> wrote in message
news:[email protected]...
>
> Book1.xls - Cell C3 =
> CONCATENATE("'",E:\Saravan\Jun2005,"\[Book2.xls]Sheet1'!$A$4:$B$18")
> Book1.xls - Cell A4:A13 have dates (01-jun-2005 to 10-jun-2005)
> Book1.xls - Cell B4:B13 should lookup a value corresponding to the date in
> Book2.xls
>
> I have tried "=VLOOKUP(A4,INDIRECT(C3),2,FALSE)", Seems to work only with
> open work books. Is there any function that could be used on closed
workbooks.
>
> Saravan
>
>

Is there a function to replace INDIRECT in microsoft excel to be used in
vlookup.

"Bob Phillips" wrote:

> INDIRECT only works with open workbooks. You could try Harlan Grove's Pull
> function, which you can get at ftp://members.aol.com/hrlngrv/pull.zip
>
> --
> HTH
>
> Bob Phillips
>
> "Saravan" <[email protected]> wrote in message
> news:[email protected]...
> >
> > Book1.xls - Cell C3 =
> > CONCATENATE("'",E:\Saravan\Jun2005,"\[Book2.xls]Sheet1'!$A$4:$B$18")
> > Book1.xls - Cell A4:A13 have dates (01-jun-2005 to 10-jun-2005)
> > Book1.xls - Cell B4:B13 should lookup a value corresponding to the date in
> > Book2.xls
> >
> > I have tried "=VLOOKUP(A4,INDIRECT(C3),2,FALSE)", Seems to work only with
> > open work books. Is there any function that could be used on closed
> workbooks.
> >
> > Saravan
> >
> >
>
>
>

On Tue, 28 Jun 2005 02:40:03 -0700, "Saravan"
<[email protected]> wrote:

>
>Book1.xls - Cell C3 =
>CONCATENATE("'",E:\Saravan\Jun2005,"\[Book2.xls]Sheet1'!$A$4:$B$18")
>Book1.xls - Cell A4:A13 have dates (01-jun-2005 to 10-jun-2005)
>Book1.xls - Cell B4:B13 should lookup a value corresponding to the date in
>Book2.xls
>
>I have tried "=VLOOKUP(A4,INDIRECT(C3),2,FALSE)", Seems to work only with
>open work books. Is there any function that could be used on closed workbooks.
>
>Saravan
>

Go to http://xcell05.free.fr/english/.

and download and install Longre's free morefunc.xll add-in. Then use the
INDIRECT.EXT function.


--ron

Thanks to all (Bob, Ron and Andy) for the information.

"Andy Wiggins" wrote:

> This file might be a help:
> http://www.bygsoftware.com/examples/...SqlRequest.zip
> It's in the "Excel with Access Databases" section on page:
> http://www.bygsoftware.com/examples/examples.htm
>
> This workbook demonstrates how to get data direct from an MS Access table,
> or from an open or closed MS Excel workbook using the workbook function
> SQL.REQUEST.
>
> Recently updated to show the use of SQL.REQUEST in the same workbook.
>
> The code is open and commented.
>
>
> --
> Andy Wiggins FCCA
> www.BygSoftware.com
> Excel, Access and VBA Consultancy
> -
>
> "Saravan" <[email protected]> wrote in message
> news:[email protected]...
> >
> > Book1.xls - Cell C3 =
> > CONCATENATE("'",E:\Saravan\Jun2005,"\[Book2.xls]Sheet1'!$A$4:$B$18")
> > Book1.xls - Cell A4:A13 have dates (01-jun-2005 to 10-jun-2005)
> > Book1.xls - Cell B4:B13 should lookup a value corresponding to the date in
> > Book2.xls
> >
> > I have tried "=VLOOKUP(A4,INDIRECT(C3),2,FALSE)", Seems to work only with
> > open work books. Is there any function that could be used on closed
> workbooks.
> >
> > Saravan
> >
> >
>
>
>

This file might be a help:
http://www.bygsoftware.com/examples/...SqlRequest.zip
It's in the "Excel with Access Databases" section on page:
http://www.bygsoftware.com/examples/examples.htm

This workbook demonstrates how to get data direct from an MS Access table,
or from an open or closed MS Excel workbook using the workbook function
SQL.REQUEST.

Recently updated to show the use of SQL.REQUEST in the same workbook.

The code is open and commented.


--
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy
-

"Saravan" <[email protected]> wrote in message
news:[email protected]...
>
> Book1.xls - Cell C3 =
> CONCATENATE("'",E:\Saravan\Jun2005,"\[Book2.xls]Sheet1'!$A$4:$B$18")
> Book1.xls - Cell A4:A13 have dates (01-jun-2005 to 10-jun-2005)
> Book1.xls - Cell B4:B13 should lookup a value corresponding to the date in
> Book2.xls
>
> I have tried "=VLOOKUP(A4,INDIRECT(C3),2,FALSE)", Seems to work only with
> open work books. Is there any function that could be used on closed
workbooks.
>
> Saravan
>
>

No, that is why I suggested Harlan's UDF.

--
HTH

Bob Phillips

"Saravan" <[email protected]> wrote in message
news:[email protected]...
>
> Is there a function to replace INDIRECT in microsoft excel to be used in
> vlookup.
>
> "Bob Phillips" wrote:
>
> > INDIRECT only works with open workbooks. You could try Harlan Grove's
Pull
> > function, which you can get at ftp://members.aol.com/hrlngrv/pull.zip
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Saravan" <[email protected]> wrote in message
> > news:[email protected]...
> > >
> > > Book1.xls - Cell C3 =
> > > CONCATENATE("'",E:\Saravan\Jun2005,"\[Book2.xls]Sheet1'!$A$4:$B$18")
> > > Book1.xls - Cell A4:A13 have dates (01-jun-2005 to 10-jun-2005)
> > > Book1.xls - Cell B4:B13 should lookup a value corresponding to the
date in
> > > Book2.xls
> > >
> > > I have tried "=VLOOKUP(A4,INDIRECT(C3),2,FALSE)", Seems to work only
with
> > > open work books. Is there any function that could be used on closed
> > workbooks.
> > >
> > > Saravan
> > >
> > >
> >
> >
> >

On Tue, 28 Jun 2005 02:40:03 -0700, "Saravan"
<[email protected]> wrote:

>
>Book1.xls - Cell C3 =
>CONCATENATE("'",E:\Saravan\Jun2005,"\[Book2.xls]Sheet1'!$A$4:$B$18")
>Book1.xls - Cell A4:A13 have dates (01-jun-2005 to 10-jun-2005)
>Book1.xls - Cell B4:B13 should lookup a value corresponding to the date in
>Book2.xls
>
>I have tried "=VLOOKUP(A4,INDIRECT(C3),2,FALSE)", Seems to work only with
>open work books. Is there any function that could be used on closed workbooks.
>
>Saravan
>

Go to http://xcell05.free.fr/english/.

and download and install Longre's free morefunc.xll add-in. Then use the
INDIRECT.EXT function.


--ron

Thanks to all (Bob, Ron and Andy) for the information.

"Andy Wiggins" wrote:

> This file might be a help:
> http://www.bygsoftware.com/examples/...SqlRequest.zip
> It's in the "Excel with Access Databases" section on page:
> http://www.bygsoftware.com/examples/examples.htm
>
> This workbook demonstrates how to get data direct from an MS Access table,
> or from an open or closed MS Excel workbook using the workbook function
> SQL.REQUEST.
>
> Recently updated to show the use of SQL.REQUEST in the same workbook.
>
> The code is open and commented.
>
>
> --
> Andy Wiggins FCCA
> www.BygSoftware.com
> Excel, Access and VBA Consultancy
> -
>
> "Saravan" <[email protected]> wrote in message
> news:[email protected]...
> >
> > Book1.xls - Cell C3 =
> > CONCATENATE("'",E:\Saravan\Jun2005,"\[Book2.xls]Sheet1'!$A$4:$B$18")
> > Book1.xls - Cell A4:A13 have dates (01-jun-2005 to 10-jun-2005)
> > Book1.xls - Cell B4:B13 should lookup a value corresponding to the date in
> > Book2.xls
> >
> > I have tried "=VLOOKUP(A4,INDIRECT(C3),2,FALSE)", Seems to work only with
> > open work books. Is there any function that could be used on closed
> workbooks.
> >
> > Saravan
> >
> >
>
>
>

No, that is why I suggested Harlan's UDF.

--
HTH

Bob Phillips

"Saravan" <[email protected]> wrote in message
news:[email protected]...
>
> Is there a function to replace INDIRECT in microsoft excel to be used in
> vlookup.
>
> "Bob Phillips" wrote:
>
> > INDIRECT only works with open workbooks. You could try Harlan Grove's
Pull
> > function, which you can get at ftp://members.aol.com/hrlngrv/pull.zip
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Saravan" <[email protected]> wrote in message
> > news:[email protected]...
> > >
> > > Book1.xls - Cell C3 =
> > > CONCATENATE("'",E:\Saravan\Jun2005,"\[Book2.xls]Sheet1'!$A$4:$B$18")
> > > Book1.xls - Cell A4:A13 have dates (01-jun-2005 to 10-jun-2005)
> > > Book1.xls - Cell B4:B13 should lookup a value corresponding to the
date in
> > > Book2.xls
> > >
> > > I have tried "=VLOOKUP(A4,INDIRECT(C3),2,FALSE)", Seems to work only
with
> > > open work books. Is there any function that could be used on closed
> > workbooks.
> > >
> > > Saravan
> > >
> > >
> >
> >
> >

This file might be a help:
http://www.bygsoftware.com/examples/...SqlRequest.zip
It's in the "Excel with Access Databases" section on page:
http://www.bygsoftware.com/examples/examples.htm

This workbook demonstrates how to get data direct from an MS Access table,
or from an open or closed MS Excel workbook using the workbook function
SQL.REQUEST.

Recently updated to show the use of SQL.REQUEST in the same workbook.

The code is open and commented.


--
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy
-

"Saravan" <[email protected]> wrote in message
news:[email protected]...
>
> Book1.xls - Cell C3 =
> CONCATENATE("'",E:\Saravan\Jun2005,"\[Book2.xls]Sheet1'!$A$4:$B$18")
> Book1.xls - Cell A4:A13 have dates (01-jun-2005 to 10-jun-2005)
> Book1.xls - Cell B4:B13 should lookup a value corresponding to the date in
> Book2.xls
>
> I have tried "=VLOOKUP(A4,INDIRECT(C3),2,FALSE)", Seems to work only with
> open work books. Is there any function that could be used on closed
workbooks.
>
> Saravan
>
>

Book1.xls - Cell C3 =
CONCATENATE("'",E:\Saravan\Jun2005,"\[Book2.xls]Sheet1'!$A$4:$B$18")
Book1.xls - Cell A4:A13 have dates (01-jun-2005 to 10-jun-2005)
Book1.xls - Cell B4:B13 should lookup a value corresponding to the date in
Book2.xls

I have tried "=VLOOKUP(A4,INDIRECT(C3),2,FALSE)", Seems to work only with
open work books. Is there any function that could be used on closed workbooks.

Saravan

Is there a function to replace INDIRECT in microsoft excel to be used in
vlookup.

"Bob Phillips" wrote:

> INDIRECT only works with open workbooks. You could try Harlan Grove's Pull
> function, which you can get at ftp://members.aol.com/hrlngrv/pull.zip
>
> --
> HTH
>
> Bob Phillips
>
> "Saravan" <[email protected]> wrote in message
> news:[email protected]...
> >
> > Book1.xls - Cell C3 =
> > CONCATENATE("'",E:\Saravan\Jun2005,"\[Book2.xls]Sheet1'!$A$4:$B$18")
> > Book1.xls - Cell A4:A13 have dates (01-jun-2005 to 10-jun-2005)
> > Book1.xls - Cell B4:B13 should lookup a value corresponding to the date in
> > Book2.xls
> >
> > I have tried "=VLOOKUP(A4,INDIRECT(C3),2,FALSE)", Seems to work only with
> > open work books. Is there any function that could be used on closed
> workbooks.
> >
> > Saravan
> >
> >
>
>
>

INDIRECT only works with open workbooks. You could try Harlan Grove's Pull
function, which you can get at ftp://members.aol.com/hrlngrv/pull.zip

--
HTH

Bob Phillips

"Saravan" <[email protected]> wrote in message
news:[email protected]...
>
> Book1.xls - Cell C3 =
> CONCATENATE("'",E:\Saravan\Jun2005,"\[Book2.xls]Sheet1'!$A$4:$B$18")
> Book1.xls - Cell A4:A13 have dates (01-jun-2005 to 10-jun-2005)
> Book1.xls - Cell B4:B13 should lookup a value corresponding to the date in
> Book2.xls
>
> I have tried "=VLOOKUP(A4,INDIRECT(C3),2,FALSE)", Seems to work only with
> open work books. Is there any function that could be used on closed
workbooks.
>
> Saravan
>
>