Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
indirect cell reference using copies of worksheets in same workboo | Excel Worksheet Functions | |||
MIN Function w/ variable address reference | Excel Worksheet Functions | |||
Using INDIRECT function to specify source data | Charts and Charting in Excel | |||
referencing named formula using INDIRECT function | Excel Worksheet Functions | |||
Dynamic Function Reference Question | Excel Discussion (Misc queries) |