Making a cell content the same as a worksheet name ...

    Hi,


    if you want use cell contents for a sheet name simply


    in cell A1, type Sheet1 with no '
    or you could type Sheet1!


    then INDIRECT(A1&"!A1") returns the value in Sheet1!A1 for the first entry in cell A1


    INDIRECT(A1&"A1") also returns the same for the second type entry in the cell A1


    is this what you want to know?


    jindon

    Hi,


    The formula


    =RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename")))


    will return the name of the worksheet in which you place the formula.


    Regards,
    Batman.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

    Just an interesting side note. These formulas only work if the workbook has been named/saved.


    Software: OpenOffice 3.0/NeoOffice 3.0 on Mac OS X 10.5.6
    Humanware: Older than dirt


    Old, slow, and confused - but at least I'm inconsistent!


    Rich
    (retired Excel 2003 user, 3.28.2008)

    Hi Simon,


    You might try this workbook code:


    Code
    Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    ActiveSheet.Range("$A$1") = ActiveSheet.Name
End Sub

    If I've been helpful, let me know. If I haven't, let me know that too. 

    Weird... it kinda works ... but not quite ....


    Batman,


    Thanks for this, it nearly works but when I COPY and PASTE this formula into the cell on one worksheet its great but when i continue with the next worksheet it updtes the first with its title ??


    For Example:


    If I have 3 Worksheets, called Sheet1, Sheet2 and Sheet3, and i paste the formula you gave me into Sheet1..... yes the cell displays "Sheet1", but if I continue onto Sheet2, this worksas well, but also updates Sheet1 to say "Sheet2" as well ? Which is of no use ?


    Strange ?


    Thanks, Any thing we missed ? Simon.


    No wrong way around ......


    Jindon,


    Thanks for the reply, but this is not quite what i was looking for, let me explain.


    I have a Spreadsheet with many Worksheets, named Sheet1, Sheet2, Sheet3, etc and I want a cell in these worksheets to display the worksheet Title, so the Title is driving the Cell content.


    I have been given some formula's like;


    =RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename")))


    but this does not work properly because it updates all Sheets with the same information, i.e. Sheet1 and Sheet2 wilhave the same detail displayed. This detail being the last update of the formula - which is weird.


    I have also been given some coded to try as follows;


    Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    ActiveSheet.Range("$A$1") = ActiveSheet.Name
    End Sub


    Hope this works ?


    Thanks for your help, any more ideas ?



    OK. Just tested it and I get a similar problem. The solution is to add the optional argument of the cell reference to the formula.


    So your formula should use cell("Filename",A1)


    to be absolutely certain it is looking at the correct cell you could use the sheet reference, but I don't think it is necessary.


    Cheers,


    Alan.

    Not great with code can you elaborate a little ...


    Dangelor,


    Thanks for the code, I/m not great on the coding side but what I did do is right click on the Worksheet Title Tab and 'View 'Code'.


    Excel defaults to this......
    ------------------------------------
    Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)


    End Sub
    ------------------------------------


    I tried to Copy over this and replace it with your code, it didn't like it, I also tried to nest the code in between the above code like this......


    ------------------------------------
    Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)


    Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    ActiveSheet.Range("$A$1") = ActiveSheet.Name
    End Sub


    End Sub
    ------------------------------------


    It doesn't like this either...... what am i doing wrong ??


    Regards, Simon.




    Quote from dangelor

    Hi Simon,


    You might try this workbook code:


    Code
    Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    ActiveSheet.Range("$A$1") = ActiveSheet.Name
End Sub
    Quote

    but what I did do is right click on the Worksheet Title Tab and 'View 'Code'.


    The code would affect all worksheets, so you need to right click on the Excel icon on the left of the menu bar and then select 'View Code'. Just remember that the code works only when a worksheet is activated, i.e. switching from one sheet to another.

    If I've been helpful, let me know. If I haven't, let me know that too. 

    This custom UDF will do it:


    Function SheetName(rAnyCell)
    Application.Volatile
    SheetName = rAnyCell.Parent.Name
    End Function


    Paste it into a new module and then gointo any cell and type
    =SheetName(A1)


    Mike



    You need to Anchor the formula to the Sheet by referencing a Cell eg


    =MID(CELL("FILENAME",A1),FIND("]",CELL("FILENAME",A1))+1,255)

    Re: Making a cell content the same as a worksheet name ...


    I tried to use VBA to insert this formula(and not just the value), and it works as a macro in a workbook, but not inside and Add-in?
    Error message is "Argument is not Optional"


    Any ideas?


    Code
    Sub InsertSheetname()
ActiveCell.Formula = _
        "=MID(CELL(""FILENAME"",A1),FIND(""]"",CELL(""FILENAME"",A1))+1,255)"
    ActiveCell.Select
End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login