include sheet name in formula
Thanks Hutch! This is cool!
"Tom Hutchins" wrote:
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" wrote:
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.
|