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

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



--
Regards,

timmulla

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

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)

would then simply be:
=VLOOKUP(A2,INDIRECT("'"&WSN&"'!A13:T144"),12,FALS E)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"timmulla" wrote:
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



--
Regards,

timmulla