VLOOKUP using the act shts tab name as a reference for the table_a

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello, I was wondering if somebody could help me with a VLOOKUP formula.

VLOOKUP(A2,'MHP 01-03-2007 '!A13:T144,12,FALSE)

Is there a way to adjust the formula above so that the MHP 01-03-2007
portion of the above formula will reference the active sheets tab name
instead of hard coded text?

any help would be appreciated.

thanks,

Tim
 
One way is to use INDIRECT with this Harlan-inspired technique to return the
active sheetname ..

Note: Workbook must be saved first

Click Insert > Name > Define
Put under "Names in workbook:": WSN
Put in the "Refers to:" box
=MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1")))+1,32)
Click OK

The above defines WSN as a name we can use to refer to the sheetname in any
sheet. It will auto-extract the sheetname implicitly. Eg, if you enter: =WSN
in any
sheet, any cell, it'll return the sheetname in that cell.

so, with the above set-up your:
VLOOKUP(A2,'MHP 01-03-2007 '!A13:T144,12,FALSE)
Click to expand...

would then simply be:
=VLOOKUP(A2,INDIRECT("'"&WSN&"'!A13:T144"),12,FALSE)

---
 
Back
Top