Use INDIRECT function to reference a value in closed file

S

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
 
B

Bob Phillips

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
 
S

Saravan

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

Bob Phillips said:
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 said:
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
Click to expand...
Click to expand...
 
R

Ron Rosenfeld

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
Click to expand...

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
 
A

Andy Wiggins

B

Bob Phillips

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

--
HTH

Bob Phillips

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

Bob Phillips said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
S

Saravan

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

Andy Wiggins said:
This file might be a help:
http://www.bygsoftware.com/examples/zipfiles/UsingSqlRequest.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 said:
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
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

Indirect( ) function loosing values when spreadsheets are closed 2
INDIRECT function to reference a named range 2
defign a function in one workbook and use it in another workbook 3
Using a cell reference to refernce worksheet in another work book 5
VLOOKUP from closed file using INDIRECT function 3
Indirect reference causes statistical function to throw up a #NUM! 10
INDIRECT(ADDRESS) inside a VLOOKUP 13
Reference a file by concatenating cell variables 13

Top