Results 1 to 14 of 14

Thread: Tab name (2003)

  1. #1
    Lounger
    Join Date
    May 2005
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Tab name (2003)

    Good morning
    I'm creating this summary of a workbook with about 80 sheets or so.
    One of the fields will be the name of the worksheet tab. Is there a quick way of copying this ??? All the rest of the information will be by linking, and rather then making a mistake typing in the worksheet tab manually, I'm hoping there is a fast and easy way

    Thanks in advance

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tab name (2003)

    The formula below will return the name of the sheet.

    <code>
    =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,20)
    </code>

    If the workbook has not been saved, it will return #Value.
    Legare Coleman

  3. #3
    Lounger
    Join Date
    May 2005
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tab name (2003)

    Thank you so much, that works great

  4. #4
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tab name (2003)

    Maybe I am unsure of what the user wanted to do. I thought it was to call the names from the tab of other sheets into a Summary sheet. I set up three sheets named Able, Baker, Charlie and a Summary sheet. Entering your formula on the Summary sheet in A1 returned the name Summary. Is the formula you suggested just to pick up the name of the current sheet or is it supposed to pick up the names of the subsequent sheets?

    Thanks.

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tab name (2003)

    It picks up the name of the sheet that is referenced in the CELL function in the formula. To pick up the name of the Able sheetg, the formula would need to be changed to:

    <code>
    =MID(CELL("filename",Able!A1),FIND("]",CELL("filename",Able!A1))+1,20)
    </code>


    Since that formula contains the name of the sheet, it might not be obvious why this would be better than just putting the name of the sheet into the cell. However, the sheet name in the formula will be updated if the sheet's name is changed, and the cell will contain the correct name.


    The other thing that could be done here would be to place the original formula in a cell on each sheet. Then reference that cell in the Summary sheet.
    Legare Coleman

  6. #6
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tab name (2003)

    Thanks so much. Will have to study, but this is very useful for some summary work I am doing.

  7. #7
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Tab name (2003)

    In your workbook create a new worksheet called Index.

    Right hand click the new tab|View code

    Paste in the following code

    <div style="width: 100%; background-color: #FFFFFF;"><font color=black>
    Private Sub Worksheet_Activate()
    Dim wSheet As Worksheet
    Dim l As Long
    l = 1

    With Me
    .Columns(1).ClearContents
    .Cells(1, 1) = "INDEX"
    .Cells(1, 1).Name = "Index"
    End With

    For Each wSheet In Worksheets
    If wSheet.Name <> Me.Name Then
    l = l + 1
    With wSheet
    .Range("A1").Name = "Start" & wSheet.Index
    .Hyperlinks.Add Anchor:=.Range("A1"), Address:="", _
    SubAddress:="Index", TextToDisplay:="Back to Index"
    End With

    Me.Hyperlinks.Add Anchor:=Me.Cells(l, 1), Address:="", _
    SubAddress:="Start" & wSheet.Index, TextToDisplay:=wSheet.Name
    End If
    Next wSheet
    End Sub
    </font color=black>
    </div hiblock>

    Return to the workbook click another tab and now activate the Index worksheet....enjoy <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Jerry

  8. #8
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Doorn, Netherlands
    Posts
    311
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tab name (2003)

    (Edited by HansV to make URL clickable - see <!help=19>Help 19<!/help>)

    The free ASAP Excel Utilities Addin (http://www.asap-utilities.com) lets you create an index of all workbook sheets automatically, and lets you jump to it when clicking the relevant entry. In addition, it contains numerous other useful tools.

    Regards,
    Teunis

  9. #9
    Silver Lounger t8ntlikly's Avatar
    Join Date
    Dec 2001
    Location
    Chandler, AZ
    Posts
    2,162
    Thanks
    46
    Thanked 13 Times in 11 Posts

    Re: Tab name (2003)

    I read this post and found it quite intriguing. One question I didnt see in there, was If I set up a workbook that lists the other workbooks, using that formula, is there a way to hyperlink that to go to a particular worksheet. I tired the hyperlink in the cell but that didnt seem to work. Great Post and extremely useful
    Thanks John
    Teamwork is essential; it gives the enemy other people to shoot at. (Murphy's War Laws #39)

  10. #10
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Doorn, Netherlands
    Posts
    311
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tab name (2003)

    John,

    The freeware ASAP Utilities lets you create an index sheet with a clickable list of all worksheets (tabs) within the present workbook. That's all. The shareware Spreadsheet Assistant (http://www.add-ins.com) goes one step further: it creates an index lists but also a clickable cell in each sheet referring to the index sheet, i.e. you can move quickly from list to the sheet and vice versa. Note that this is also limited to sheets within a single workbook.
    Hopefully this answers your question.

    Regards, Teunis

  11. #11
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tab name (2003)

    Hyperlinks are easy to make for any cell. If you already have the sheet names listed in some cells, do Ctrl+k to get to the hyperlink menu (or right-click on the cell and pick Hyperlink). In the new menu, look at the "Link to" fields on the left side, select "Place in this document". Now in "type the cell reference field" select the sheet and cell you want the link to go to from the choices in the text box. You can change the name shown for the link to anything you want (from the Text To Display field).

    Deb

  12. #12
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Kitchener, Ontario, Canada
    Posts
    228
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tab name (2003)

    This works great.... now how would you add to it to take a cell from each page and show that as well on the index page you created. I have added this into a file which has about 100 worksheets. I would like to see the same Cell (B10) right next to the coresponding information on the index page.

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Tab name (2003)

    Immediately above the line
    <code>
    End With
    </code>
    insert the following line:
    <code>
    Me.Range("B" & I) = .Range("B10")</code>

  14. #14
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Tab name (2003)

    Hi Corey

    This should do it

    Private Sub Worksheet_Activate()
    Dim wSheet As Worksheet
    Dim l As Long
    Dim j As Variant
    l = 1

    With Me
    .Columns(1).ClearContents
    .Cells(1, 1) = "INDEX"
    .Cells(1, 1).Name = "Index"
    End With

    For Each wSheet In Worksheets
    If wSheet.Name <> Me.Name Then
    l = l + 1
    With wSheet
    .Range("A1").Name = "Start" & wSheet.Index

    .Hyperlinks.Add Anchor:=.Range("A1"), Address:="", _
    SubAddress:="Index", TextToDisplay:="Back to Index"
    j = wSheet.Cells(10,2).Value
    End With

    Me.Hyperlinks.Add Anchor:=Me.Cells(l, 1), Address:="", _
    SubAddress:="Start" & wSheet.Index, TextToDisplay:=wSheet.Name
    Me.Cells(l, 2).Value = j
    End If
    Next wSheet
    End Sub


    Added items in bold...I made j a variant as I was unsure what you wanted to add, but I guess it is text so change variant to string
    Jerry

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •