Returning sheet name from formula - Page 2
Eliminate Pivot Table Annoyances
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 18 of 18

Thread: Returning sheet name from formula

  1. #11
    Board Regular
    Join Date
    Feb 2002
    Posts
    202
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    is this what you're looking for? it returns the sheet name:
    =RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1),1))

  2. #12
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    anno,

    I think Mudface wants wants to pull the sheet name out of a formula. Your code will return the name of the sheet that contains the cell.

    [ This Message was edited by: Al Chara on 2002-04-11 15:45 ]

  3. #13
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yes, Al, you have it right, I want the sheet name from the formula. I can do it in VB (thanks for your example too Al), but I just wondered if there was another way, as people here tend to get put off when a workbook contains macros.

  4. #14
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Mudface,

    The key is getting a function that will look at the formula and not the value. What about writing a UDF?

    [ This Message was edited by: Al Chara on 2002-04-12 08:41 ]

  5. #15
    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 Mudface,

    How about this?

    ----------------
    Function GetSheetfromFormula(refcell As Range)

    Dim findexclaim As Integer
    If refcell.Cells.Count <> 1 Or Not refcell.HasFormula Then
    GetSheetfromFormula = CVErr(xlErrValue)
    Exit Function
    End If

    On Error Resume Next
    findexclaim = WorksheetFunction.Search("!", refcell.Formula, 1)
    On Error GoTo 0
    If findexclaim = 0 Then
    GetSheetfromFormula = Application.Caller.Parent.Name
    Else
    GetSheetfromFormula = Mid(refcell.Formula, 2, findexclaim - 2)
    End If
    End Function
    ---------------------

    I imagine that this could be expanded to handle 3-D references and for finding multiple sheetnames within a complex formula.

    Bye,
    Jay

  6. #16
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    357
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

  7. #17
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Cosmos,

    We know how to do this in VBA. But the real question is how do you do it with just a formula in the worksheet. I am going to start a new thread with that question.

  8. #18
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    This seems to have been shunted down to the bottom of the board by the date error that was mentioned and I've only just found it . Thanks to all who replied, especially Al, I'll give this a go. Cheers .

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