formula to find Sheet name in different worksheet

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: formula to find Sheet name in different worksheet

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Brisbane, Down Under
    Posts
    542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Ivan (with greatful thanks) posted this formula recently which places the worksheet name into a cell which I have used more and more of late.

    =MID(CELL("filename",C10),FIND("]",CELL("filename"),1)+1,255)

    I have 2 variations I need to use.
    1) I want the cell to reference a different worksheet (which is a department name), have tried a number of alternatives but am having no luck.
    2) I then require another cell which is a formula (for use in a graph) to pick up this same reference ie replace the Network Program Delivery in ="Network Program Delivery'!$K$1 with the worksheet reference.

    I know it seems to be a heavy handed way of doing things (and there probably is some simple way of doing it) but the way department names change regularily around here I only want to make one change and that is to the worksheet name.

    Will be eternally gratefull if someone can supply the info
    Edit: too early in the morning here-also off the coffee which probably explains it. Prob no2 is not a prob any more, will just rely on the formula automatically changing with the worksheet name change

    [ This Message was edited by: SamS on 2002-05-21 15:49 ]

  2. #2
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    You would likely need a user-defined function (UDF) to do this, although I can't tell exactly what you are requesting.


    Function test()
    test = Application.Caller.Worksheet.Next.Name
    End Function

    Function test2()
    test2 = ActiveSheet.Next.Name
    End Function


    These two functions should work for you, after modification. I think the top is a bit more stable. These just get the next sheet after the sheet where you place the formula.

    Bye,
    Jay

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Brisbane, Down Under
    Posts
    542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Thanks Jay, will play around with this and see what comes up.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com