If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
include sheet name in formula
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. |
#2
|
|||
|
|||
include sheet name in formula
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. |
#3
|
|||
|
|||
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. |
Thread Tools | |
Display Modes | |
|
|