Perfect! I was so close, yet so far away :-)

Thanks Paul.
--
Chuck M.


"PCLIVE" wrote:

Try this:

=VLOOKUP(CurrentMonth,INDIRECT("'" & A9 & "'!$AE41:AF52"),2,FALSE)

HTH,
Paul

"Chuck M" wrote in message
...
I'm using VLOOKUP to look up a value on another worksheet. The lookup
works
fine when I use a constant for the sheet name but I get 'The Formula You
Typed Contains an Error' message when I try to pull the sheet name from a
cell on the current worksheet.

The following works fine
=VLOOKUP(CurrentMonth,'Cost of Goods Sold'!$AE41:AF52,2,FALSE)

Cell A9 on the current worksheet contains the sheet name Cost of Goods
Sold.
This formula does not work:
=VLOOKUP(CurrentMonth,"'"&INDIRECT("A9")&"'!"&$AE$ 41:$AF$52,2,FALSE)

Do I have a simple syntax error here or can I not use INDIRECT in this
manner?

--
TIA
Chuck M.