VLOOKUP

K

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
 
J

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)
 
J

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
 
K

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?

Jacob Skaria said:
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


Kevin McCartney said:
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
Click to expand...
Click to expand...
 
K

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
 
K

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


Jacob Skaria said:
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


Kevin McCartney said:
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
Click to expand...
Click to expand...
 
J

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


Kevin McCartney said:
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


Jacob Skaria said:
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


Kevin McCartney said:
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
Click to expand...
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

Need VLOOKUP to Work Two Ways 0
my vlookup path changes when I open the file 1
vlookup Function 2
Vlookup to reference offset 1
Help with If and Vlookup Statement 1
Help with Vlookup Match and IF statement 2
Help with IF and VLookup Match Statement 1
Combine data into central worksheet 1

Top