Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How can I 'read' a formula? (2000)

    How can I refer to a cell that contains a formula, and extract the worksheet reference that the linked formula contains?? For example, if A1 contains '=Sheet1!D4' how can I get the word Sheet1 to display in cell A2? Andy.

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

    Re: How can I 'read' a formula? (2000)

    Formulas can be very complex, so I wonder what the use of this is. Not all formulas are of the form =SheetName!CellReference.

    You can use a custom function to return the formula of a cell:

    Public Function GetFormula(oCell As Range) As String
    If oCell.HasFormula Then
    GetFormula = oCell.Formula
    End If
    End Function

    Say that cell A1 contains the formula =Sheet1!D4.
    In cell A2, enter the formula =GetFormula(A1). A2 will display the formula as text.
    In cell A3, you can enter a formula to extract the sheet name. A crude version is

    =MID(A2,2,FIND("!",A2)-2)

    This could be refined to catch errors (for example if A1 does not contain a formula, or the formula does not refer to another cell, or if the sheet name contains spaces, ...

  3. #3
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How can I 'read' a formula? (2000)

    I was hoping that this could be done directly in Excel, without programming? Isn't there a way of reading the worksheet name contained in a formula?

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How can I 'read' a formula? (2000)

    Well, it can be done, sort of.

    If your cell to "evaluate" is cell A2 and you are in the cell to the immediate right (B2), define this name:

    Name: GetSheetRef
    Refers To: =MID(GET.CELL(6,A2),2,FIND("!",GET.CELL(6,A2))-2)

    Now in cell B2 enter

    =GetSheetRef

    Do NOT copy cell B2 to another worksheet, or Excel will CRASH.

    You can now enter =GetSheetRef in any cell and it will extract the sheetname of the formula (of that simple format) of the cell to the immediate left.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

    Re: How can I 'read' a formula? (2000)

    There are add-ins, such as MoreFunc, that let you retrieve the formula of a cell without programming yourself (the programming has been done by the developer of the add-in)

    What about formulas such as =Sheet1!A1+Sheet2!A1 or =Sum(Sheet1:Sheet5!A1) or =INDIRECT("Sheet"&A1&"!A3"). In the latter case, if A1 contains 9, the formula refers to Sheet9. It won't be easy to handle all these cases.

Posting Permissions

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