include sheet name in formula

  • Thread starter Thread starter pm
  • Start date Start date
P

pm

I have multiple sheets in a file and i'm trying to include the sheet name in
a cell with a formula but not sure if this can be done - for instance:

Company Vendor Date Invoice
1 1179 20100515 20100515Janitorial

in this example in the invoice column i want to include the date and the
sheet name. Thanks for any assistance.
 
One way is to use INDIRECT with this Harlan-inspired technique to return the
active sheetname. Note: Workbook must be saved first

Select cell A1 (important!)
Click Insert > Name > Define
(in XL2007, it's Formula ribbon >> Defined Names section >> Define Name)
Put under "Names in workbook:": WSN
(in XL2007, Name=WSN, Scope=Workbook)
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.

If the first invoice cell on your worksheet is D2, its formula would then be:
=C2&WSN

Hope this helps,

Hutch
 
Thanks Hutch! This is cool!

Tom Hutchins said:
One way is to use INDIRECT with this Harlan-inspired technique to return the
active sheetname. Note: Workbook must be saved first

Select cell A1 (important!)
Click Insert > Name > Define
(in XL2007, it's Formula ribbon >> Defined Names section >> Define Name)
Put under "Names in workbook:": WSN
(in XL2007, Name=WSN, Scope=Workbook)
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.

If the first invoice cell on your worksheet is D2, its formula would then be:
=C2&WSN

Hope this helps,

Hutch

pm said:
I have multiple sheets in a file and i'm trying to include the sheet name in
a cell with a formula but not sure if this can be done - for instance:

Company Vendor Date Invoice
1 1179 20100515 20100515Janitorial

in this example in the invoice column i want to include the date and the
sheet name. Thanks for any assistance.
Click to expand...
Click to expand...
 
Back
Top