Use INDIRECT function to reference a value in closed file

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" <Saravan@discussions.microsoft.com> wrote in message
news:4E4DD2CE-386F-4237-8596-9CE8698906EE@microsoft.com...
>
> 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" <Saravan@discussions.microsoft.com> wrote in message
> news:4E4DD2CE-386F-4237-8596-9CE8698906EE@microsoft.com...
> >
> > 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"
<Saravan@discussions.microsoft.com> 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

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" <Saravan@discussions.microsoft.com> wrote in message
news:4E4DD2CE-386F-4237-8596-9CE8698906EE@microsoft.com...
>
> 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" <Saravan@discussions.microsoft.com> wrote in message
news:7087C577-3A32-41FA-938B-C1F74C26D7A9@microsoft.com...
>
> 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" <Saravan@discussions.microsoft.com> wrote in message
> > news:4E4DD2CE-386F-4237-8596-9CE8698906EE@microsoft.com...
> > >
> > > 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
> > >
> > >
> >
> >
> >

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" <Saravan@discussions.microsoft.com> wrote in message
> news:4E4DD2CE-386F-4237-8596-9CE8698906EE@microsoft.com...
> >
> > 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

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" <Saravan@discussions.microsoft.com> wrote in message
news:4E4DD2CE-386F-4237-8596-9CE8698906EE@microsoft.com...
>
> 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" <Saravan@discussions.microsoft.com> wrote in message
> news:4E4DD2CE-386F-4237-8596-9CE8698906EE@microsoft.com...
> >
> > 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"
<Saravan@discussions.microsoft.com> 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

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" <Saravan@discussions.microsoft.com> wrote in message
news:4E4DD2CE-386F-4237-8596-9CE8698906EE@microsoft.com...
>
> 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" <Saravan@discussions.microsoft.com> wrote in message
news:7087C577-3A32-41FA-938B-C1F74C26D7A9@microsoft.com...
>
> 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" <Saravan@discussions.microsoft.com> wrote in message
> > news:4E4DD2CE-386F-4237-8596-9CE8698906EE@microsoft.com...
> > >
> > > 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
> > >
> > >
> >
> >
> >

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" <Saravan@discussions.microsoft.com> wrote in message
> news:4E4DD2CE-386F-4237-8596-9CE8698906EE@microsoft.com...
> >
> > 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
> >
> >
>
>
>