Vlookup when table_array is value in cell

[mattyblueice]

Hi there!

I am working on a project summary dashboard and I am having an issue with the vlookup reference. In the workbook, there are multiple sheets: Proj A, Proj B, Proj C, Proj D, etc. The cell references are all the same in each of the sheets for the vlookup to reference and I wanted the first part of the table array to be based on the value from a drop down.

The dashboard page has a cell dropdown list for the user to select from Proj A, Proj B, etc. That value would be in Cell B1. Can I somehow concatenate the reference the value in cell B1 with the rest of the table array?

My formula: =Vlookup(A11,**value in Cell B1**A3:F182,2,false)

I tried using the INDIRECT feature with no luck.

Thank you for your time!

 

[Gerald Higgins]

Try

=VLOOKUP(A11,INDIRECT(B1&"!A3:F182"),2,FALSE)

 

[mattyblueice]

Good Morning, I put that formula in and I got a reference error and when I evaluated it the #REF ! is related to table_array, Has this worked before for you?

 

[mattyblueice]

UREKA! I got it to work I forgot to include the " ' "before and after the sheet name so Excel knows its a sheet reference. My formula looks like this:
=VLOOKUP(A11,INDIRECT("'"&B1&"'"&"!A3:F182"),2,FALSE)

Thanks for your help!

 

[Gerald Higgins]

Ah yes, sorry, I tested this on a version where my sheet name was a single word, and I think you don't need the " ' " characters, but if your sheet name has multiple words with one or more space between, you will need them.