Need help please with VLOOKUP and INDIRECT question
 

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.

Need help please with VLOOKUP and INDIRECT question
 

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.

Need help please with VLOOKUP and INDIRECT question
 

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?

Need help please with VLOOKUP and INDIRECT question
 

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.

Need help please with VLOOKUP and INDIRECT question
 

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.