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

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)

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

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