0

I Have a cell (lets say its A1) that specifies (from a drop-down list) a Sheet Name (AA BB CC DD etc) I want to pass that sheet name to a VLOOKUP Formula which would (if life were simple) be SOMETHING LIKE =VLOOKUP(B5,A1&!D1:E22,2,False) Where B5 is the cell containing the Lookup_value, A1 is the cell containing the sheet name, and D1:E22 is the Table array (it is the same on each sheet!).

I have been told the INDIRECT formula should work, Or even TABLES? but I am yet to find a way to get a successful result.

Anyone Know? Thanks

1

Yes, you need something like this:

=VLOOKUP(B5,"'"&A1&"'!D1:E22"),2,False)

In other words, you need to pass your cell reference as a string to INDIRECT. This will get trickier if the range you need to reference ever changes - you'll have to remember to update your formula. Also note that using this function makes your spreadsheet 'volatile', meaning that it will have to recalculate the entire workbook whenever you change anything. This can get to be a problem for big workbooks.

  • You should wrap it in apostrophes in case there's a space in the sheet name. INDIRECT("'"&A1&"'!A1") – Engineer Toast Mar 27 '15 at 15:14
  • Yes - thanks. I am an idiot. Edited. – benshepherd Mar 27 '15 at 15:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.