Use INDIRECT function to reference a value in closed file
 

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" wrote in message
...

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" wrote in message
...

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"
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" wrote in message
...

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" wrote in message
...

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" wrote in message
...

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" wrote in message
...

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