Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Extract Sheet Name From Formula (Office 2000-2003)

    Is there a way to extract the sheet name from a formula such as +'Sheetname'!A1? I've tried variations of =Left, Right, and Mid but I always end up with the portion of the solution rather than the text embedded in the formula.

    TIA
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  2. #2
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Extract Sheet Name From Formula (Office 2000-2

    Can I ask the purpose?
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extract Sheet Name From Formula (Office 2000-2

    I have a sheet that summarizes a cell from many tabs. The tab name happens to be the Dept. # - which is information I need.
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

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

    Re: Extract Sheet Name From Formula (Office 2000-2003)

    You would have to use a User Defined Function. A UDF can extract the formula from a cell and work on it. The formula in the cell would have to be consistent enough for the UDF to be able to find the sheet name in the formula. I could help with the UDF, but would have to know exactly what the formula looked like.
    Legare Coleman

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extract Sheet Name From Formula (Office 2000-2003)

    The formula is +'Sheetname'!A1 where sheetname is 5-digits - i.e. 10100, 15000, 23100 etc. I would like to end up with a column next to the formula showing the text 10100 etc. Am I making sense? 10100 etc. is a department name/number to me and I need that info along with the $ for that department.
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

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

    Re: Extract Sheet Name From Formula (Office 2000-2003)

    Does this give you what you want:

    <code>
    Public Function GetDept(oCell As Range) As String
    Dim strWk As String
    If oCell.HasFormula Then
    strWk = oCell.Formula
    If InStr(strWk, "!") > 0 Then
    GetDept = Mid(strWk, InStr(strWk, "!") - 6, 5)
    Else
    GetDept = CVErr(2001)
    End If
    Else
    GetDept = CVErr(2001)
    End If
    End Function
    </code>

    Use like this:

    <code>
    =GETDEPT(A1)
    </code>

    where A1 is the cell containing the formula.
    Legare Coleman

  7. #7
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extract Sheet Name From Formula (Office 2000-2003)

    Yes, that works great! Thanks much! Can you explain the CVErr(2001)?
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

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

    Re: Extract Sheet Name From Formula (Office 2000-2003)

    That will return a #Value error if the cell does not contain a formula, or if the formula does not contain a sheetname.
    Legare Coleman

  9. #9
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extract Sheet Name From Formula (Office 2000-2003)

    Got it! Thanks again.
    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

Posting Permissions

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