take sheet name from a cell in VLOOKUP
 

Hi,

is it possible to use the value from a cell as the sheet name in a VLOOKUP
function?

for example, the formula is -
=VLOOKUP(A17,[Book1]August09!$A$1:$E$11,2,FALSE). In this formula, the
"August09" part is the one that I want to be able to select in a dropdown.
Whatever month I select in the dropdown, the formula should take the data
from the sheet named as the one I selected.

any help would be appreciated.

Thanks

take sheet name from a cell in VLOOKUP
 

Gaurav wrote:
Hi,

is it possible to use the value from a cell as the sheet name in a VLOOKUP
function?

for example, the formula is -
=VLOOKUP(A17,[Book1]August09!$A$1:$E$11,2,FALSE). In this formula, the
"August09" part is the one that I want to be able to select in a dropdown.
Whatever month I select in the dropdown, the formula should take the data
from the sheet named as the one I selected.

any help would be appreciated.

Thanks



Assuming your drop down is in B17:

=VLOOKUP(A17,INDIRECT("[Book1]"&B17&"!$A$1:$E$11"),2,FALSE)

take sheet name from a cell in VLOOKUP
 

Look in the help index for INDIRECT and be aware that it does not work on
closed files. Or, you could use editreplace and still use on closed.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Gaurav" wrote in message
...
Hi,

is it possible to use the value from a cell as the sheet name in a VLOOKUP
function?

for example, the formula is -
=VLOOKUP(A17,[Book1]August09!$A$1:$E$11,2,FALSE). In this formula, the
"August09" part is the one that I want to be able to select in a dropdown.
Whatever month I select in the dropdown, the formula should take the data
from the sheet named as the one I selected.

any help would be appreciated.

Thanks

take sheet name from a cell in VLOOKUP
 

The INDIRECT function is probably what you're looking for. If the sheet name
is in cell A1, you would replace [Book1]August09!$A$1:$E$11 with
INDIRECT("'[Book1]"&A1&"'!$A$1:$E$11") . The entire VLOOKUP formula would
therefore be =VLOOKUP(A17,INDIRECT("'[Book1]"&A1&"'!$A$1:$E$11"),2,FALSE).

Note that Book1 has to be open for this formula to work properly.
--
Rob Jordan
Powered by Creative Laziness


"Gaurav" wrote:

Hi,

is it possible to use the value from a cell as the sheet name in a VLOOKUP
function?

for example, the formula is -
=VLOOKUP(A17,[Book1]August09!$A$1:$E$11,2,FALSE). In this formula, the
"August09" part is the one that I want to be able to select in a dropdown.
Whatever month I select in the dropdown, the formula should take the data
from the sheet named as the one I selected.

any help would be appreciated.

Thanks