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
|
|||
|
|||
tab name = cell value
is it possible to have a cell value equal the same as a sheet (tab) name?
just something simple as =sheet1 or something thank you, |
#2
|
|||
|
|||
tab name = cell value
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
best wishes Sreedhar "jatman" wrote: is it possible to have a cell value equal the same as a sheet (tab) name? just something simple as =sheet1 or something thank you, |
#3
|
|||
|
|||
tab name = cell value
I don't know how, & I can't remember who to credit with the solution but
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) is the formula to use. I believe the workbook has to be saved at least once for this to work. Cheers Paul "jatman" wrote: is it possible to have a cell value equal the same as a sheet (tab) name? just something simple as =sheet1 or something thank you, |
#4
|
|||
|
|||
tab name = cell value
"jatman" wrote:
is it possible to have a cell value equal the same as a sheet (tab) name? just something simple as =sheet1 or something Another technique, credits to Harlan, which enables usage for all sheets at one go (same proviso - book must be saved beforehand) 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 formulas. It will auto-extract the sheetname implicitly. Then test/use in any sheet, in any cell, eg: =WSN will return the particular sheetname -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#5
|
|||
|
|||
tab name = cell value
You can do other, similar, things too:
http://www.mcgimpsey.com/excel/formu..._function.html Regards, Ryan-- -- RyGuy "Max" wrote: "jatman" wrote: is it possible to have a cell value equal the same as a sheet (tab) name? just something simple as =sheet1 or something Another technique, credits to Harlan, which enables usage for all sheets at one go (same proviso - book must be saved beforehand) 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 formulas. It will auto-extract the sheetname implicitly. Then test/use in any sheet, in any cell, eg: =WSN will return the particular sheetname -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#6
|
|||
|
|||
tab name = cell value
Is it possible to include this as a function in "Personal" so that it is
available to all workbooks, perhaps with some error checking for unsaved workbooks? Cheers Paul "Max" wrote: "jatman" wrote: is it possible to have a cell value equal the same as a sheet (tab) name? just something simple as =sheet1 or something Another technique, credits to Harlan, which enables usage for all sheets at one go (same proviso - book must be saved beforehand) 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 formulas. It will auto-extract the sheetname implicitly. Then test/use in any sheet, in any cell, eg: =WSN will return the particular sheetname -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#7
|
|||
|
|||
tab name = cell value
Function SheetName(Optional ByVal rng As Range) As String
Application.Volatile If rng Is Nothing Then Set rng = Application.Caller SheetName = rng.Parent.Name End Function =SheetName() Has no arguments and doesn't care if the workbook is saved. Gord Dibben MS Excel MVP On Thu, 21 Feb 2008 11:51:02 -0800, Paul Moles wrote: Is it possible to include this as a function in "Personal" so that it is available to all workbooks, perhaps with some error checking for unsaved workbooks? Cheers Paul "Max" wrote: "jatman" wrote: is it possible to have a cell value equal the same as a sheet (tab) name? just something simple as =sheet1 or something Another technique, credits to Harlan, which enables usage for all sheets at one go (same proviso - book must be saved beforehand) 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 formulas. It will auto-extract the sheetname implicitly. Then test/use in any sheet, in any cell, eg: =WSN will return the particular sheetname -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#8
|
|||
|
|||
tab name = cell value
Thanks Gordon,
this really works fine... even in a Spanish Excel Gracias, Fernando "Gord Dibben" wrote: Function SheetName(Optional ByVal rng As Range) As String Application.Volatile If rng Is Nothing Then Set rng = Application.Caller SheetName = rng.Parent.Name End Function =SheetName() Has no arguments and doesn't care if the workbook is saved. Gord Dibben MS Excel MVP On Thu, 21 Feb 2008 11:51:02 -0800, Paul Moles wrote: Is it possible to include this as a function in "Personal" so that it is available to all workbooks, perhaps with some error checking for unsaved workbooks? Cheers Paul "Max" wrote: "jatman" wrote: is it possible to have a cell value equal the same as a sheet (tab) name? just something simple as =sheet1 or something Another technique, credits to Harlan, which enables usage for all sheets at one go (same proviso - book must be saved beforehand) 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 formulas. It will auto-extract the sheetname implicitly. Then test/use in any sheet, in any cell, eg: =WSN will return the particular sheetname -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Thread Tools | |
Display Modes | |
|
|