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.

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.

One way:

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

In article ,
Chuck M wrote:

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?

Chuck

The indirect argument needs to evaluate to an address, and it will if
you do your concatenation inside the parentheses. So,

=VLOOKUP(CurrentMonth,"'"&INDIRECT("A9"&"'!"&$AE$4 1:$AF$52),2,FALSE)

should work.

Good luck.

Ken
Norfolk, Va



On Jun 8, 11:10 am, Chuck M wrote:
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.

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.