VLOOKUP

[Kevin McCartney]

Hi TWIMC,

Usually when referencing into a different workbook, Excel constructs the
formula like so:

=VLOOKUP(B10;'C:\folder path\My
Documents\[workbookname.xls]worksheetname'!$G$10:$H$20;2;FALSE)

My question is, is there a way to have a VLOOKUP formula reference the table
array parameter in the following manner?

=VLOOKUP(B10;A2!$G$10:$H$20;2;FALSE)

where A2 = 'C:\folder path\My Documents\[workbookname.xls]worksheetname'

This would allow the end user to specify which workbook/worksheet the
VLOOKUP function is to reference.


TIA
KM

 

[Jacob Skaria]

Use INDIRECT() function

Try the below which will help ...

In Book1, Sheet1, cell A1 type: "This is a test" (without the quotation
marks).
In Book2, Sheet1, cell A1 type: "Book1" (without the quotation marks).
In Book2, Sheet1, cell A2 type: "Sheet1" (without the quotation marks).
In Book2, Sheet1, cell A3 type: "A1" (without the quotation marks).
Save both workbooks.
In Book2, Sheet1, cell B1 type the following formula:

=INDIRECT("'["&A1&".xls]"&A2&"'!"&A3)

 

[Alan McQuaid via OfficeKB.com]

Hi Kevin,

You need to use the INDIRECT function here

Instead of:
=VLOOKUP(B10;A2!$G$10:$H$20;2;FALSE)

Use:
=VLOOKUP(B10,INDIRECT(A2&"!$G$10:$H$20"),2,FALSE)

Alan

 

[jaf]

Hi Kevin,
Indirect will work as Jacob & Alan suggested, but keep in mind the other workbook has to be open for indirect to work.

John

 

[Kevin McCartney]

Like Jaf points out, is there a function that works when the reference file
is closed, or does it work regardles in Excel 2007?

Use INDIRECT() function

Try the below which will help ...

In Book1, Sheet1, cell A1 type: "This is a test" (without the quotation
marks).
In Book2, Sheet1, cell A1 type: "Book1" (without the quotation marks).
In Book2, Sheet1, cell A2 type: "Sheet1" (without the quotation marks).
In Book2, Sheet1, cell A3 type: "A1" (without the quotation marks).
Save both workbooks.
In Book2, Sheet1, cell B1 type the following formula:

=INDIRECT("'["&A1&".xls]"&A2&"'!"&A3)


--
If this post helps click Yes
---------------
Jacob Skaria

Hi TWIMC,

Usually when referencing into a different workbook, Excel constructs the
formula like so:

=VLOOKUP(B10;'C:\folder path\My
Documents\[workbookname.xls]worksheetname'!$G$10:$H$20;2;FALSE)

My question is, is there a way to have a VLOOKUP formula reference the table
array parameter in the following manner?

=VLOOKUP(B10;A2!$G$10:$H$20;2;FALSE)

where A2 = 'C:\folder path\My Documents\[workbookname.xls]worksheetname'

This would allow the end user to specify which workbook/worksheet the
VLOOKUP function is to reference.


TIA
KM

 

[Kevin McCartney]

Like Jaf points out, is there a function that works when the referenced file
is closed, or does it work regardles in Excel 2007?

TIA
KM

 

[Kevin McCartney]

Like Jaf points out, is there a function that works when the referenced file
is closed, or does it work regardles in Excel 2007?

TIA
KM

Use INDIRECT() function

Try the below which will help ...

In Book1, Sheet1, cell A1 type: "This is a test" (without the quotation
marks).
In Book2, Sheet1, cell A1 type: "Book1" (without the quotation marks).
In Book2, Sheet1, cell A2 type: "Sheet1" (without the quotation marks).
In Book2, Sheet1, cell A3 type: "A1" (without the quotation marks).
Save both workbooks.
In Book2, Sheet1, cell B1 type the following formula:

=INDIRECT("'["&A1&".xls]"&A2&"'!"&A3)


--
If this post helps click Yes
---------------
Jacob Skaria

Hi TWIMC,

Usually when referencing into a different workbook, Excel constructs the
formula like so:

=VLOOKUP(B10;'C:\folder path\My
Documents\[workbookname.xls]worksheetname'!$G$10:$H$20;2;FALSE)

My question is, is there a way to have a VLOOKUP formula reference the table
array parameter in the following manner?

=VLOOKUP(B10;A2!$G$10:$H$20;2;FALSE)

where A2 = 'C:\folder path\My Documents\[workbookname.xls]worksheetname'

This would allow the end user to specify which workbook/worksheet the
VLOOKUP function is to reference.


TIA
KM

 

[Jacob Skaria]

You can download an add-in called Morefunc which has a function called
INDIRECT.EXT that will work if the source book is closed

http://xcell05.free.fr/morefunc/english/index.htm


If this post helps click Yes
---------------
Jacob Skaria

Like Jaf points out, is there a function that works when the referenced file
is closed, or does it work regardles in Excel 2007?

TIA
KM

Use INDIRECT() function

Try the below which will help ...

In Book1, Sheet1, cell A1 type: "This is a test" (without the quotation
marks).
In Book2, Sheet1, cell A1 type: "Book1" (without the quotation marks).
In Book2, Sheet1, cell A2 type: "Sheet1" (without the quotation marks).
In Book2, Sheet1, cell A3 type: "A1" (without the quotation marks).
Save both workbooks.
In Book2, Sheet1, cell B1 type the following formula:

=INDIRECT("'["&A1&".xls]"&A2&"'!"&A3)


--
If this post helps click Yes
---------------
Jacob Skaria

Hi TWIMC,

Usually when referencing into a different workbook, Excel constructs the
formula like so:

=VLOOKUP(B10;'C:\folder path\My
Documents\[workbookname.xls]worksheetname'!$G$10:$H$20;2;FALSE)

My question is, is there a way to have a VLOOKUP formula reference the table
array parameter in the following manner?

=VLOOKUP(B10;A2!$G$10:$H$20;2;FALSE)

where A2 = 'C:\folder path\My Documents\[workbookname.xls]worksheetname'

This would allow the end user to specify which workbook/worksheet the
VLOOKUP function is to reference.


TIA
KM