+ Reply to Thread
Results 1 to 4 of 4

Searching TEXT in formulae, rather than results of formulae

  1. #1
    Niek Otten
    Guest

    Re: Searching TEXT in formulae, rather than results of formulae

    You can use this UDF:

    Function ShowFormula(a As Range)
    If Application.ReferenceStyle = xlR1C1 _
    Then ShowFormula = a.FormulaR1C1 _
    Else: ShowFormula = a.Formula
    End Function

    You may benefit from (temporarily) choosing R1C1 reference style
    (Tools>Options, General tab). If for example you always reference the row
    above or the column to the left, the formulas are the same and exeptions are
    easily detected.

    --
    Kind regards,

    Niek Otten

    Microsoft MVP - Excel

    "AndyE" <AndyE@discussions.microsoft.com> wrote in message
    news:17EA0933-0333-430C-8080-73168A44F235@microsoft.com...
    > Is there any way to use the "=RIGHT" or "=LEFT" functions to search the
    > text
    > that makes up a formula, rather than the result of the formula?
    >
    > I have a list of formulae that reference a sequence of cells, and I need
    > to
    > check that the sequence isn't broken (I'm going to ask it to check that
    > each
    > cell reference is one away from the previous reference, and return an
    > error
    > message if it isn't).
    >
    > Thanks!




  2. #2
    Niek Otten
    Guest

    Re: Searching TEXT in formulae, rather than results of formulae

    You can use this UDF:

    Function ShowFormula(a As Range)
    If Application.ReferenceStyle = xlR1C1 _
    Then ShowFormula = a.FormulaR1C1 _
    Else: ShowFormula = a.Formula
    End Function

    You may benefit from (temporarily) choosing R1C1 reference style
    (Tools>Options, General tab). If for example you always reference the row
    above or the column to the left, the formulas are the same and exeptions are
    easily detected.

    --
    Kind regards,

    Niek Otten

    Microsoft MVP - Excel

    "AndyE" <AndyE@discussions.microsoft.com> wrote in message
    news:17EA0933-0333-430C-8080-73168A44F235@microsoft.com...
    > Is there any way to use the "=RIGHT" or "=LEFT" functions to search the
    > text
    > that makes up a formula, rather than the result of the formula?
    >
    > I have a list of formulae that reference a sequence of cells, and I need
    > to
    > check that the sequence isn't broken (I'm going to ask it to check that
    > each
    > cell reference is one away from the previous reference, and return an
    > error
    > message if it isn't).
    >
    > Thanks!




  3. #3
    AndyE
    Guest

    Searching TEXT in formulae, rather than results of formulae

    Is there any way to use the "=RIGHT" or "=LEFT" functions to search the text
    that makes up a formula, rather than the result of the formula?

    I have a list of formulae that reference a sequence of cells, and I need to
    check that the sequence isn't broken (I'm going to ask it to check that each
    cell reference is one away from the previous reference, and return an error
    message if it isn't).

    Thanks!

  4. #4
    Niek Otten
    Guest

    Re: Searching TEXT in formulae, rather than results of formulae

    You can use this UDF:

    Function ShowFormula(a As Range)
    If Application.ReferenceStyle = xlR1C1 _
    Then ShowFormula = a.FormulaR1C1 _
    Else: ShowFormula = a.Formula
    End Function

    You may benefit from (temporarily) choosing R1C1 reference style
    (Tools>Options, General tab). If for example you always reference the row
    above or the column to the left, the formulas are the same and exeptions are
    easily detected.

    --
    Kind regards,

    Niek Otten

    Microsoft MVP - Excel

    "AndyE" <AndyE@discussions.microsoft.com> wrote in message
    news:17EA0933-0333-430C-8080-73168A44F235@microsoft.com...
    > Is there any way to use the "=RIGHT" or "=LEFT" functions to search the
    > text
    > that makes up a formula, rather than the result of the formula?
    >
    > I have a list of formulae that reference a sequence of cells, and I need
    > to
    > check that the sequence isn't broken (I'm going to ask it to check that
    > each
    > cell reference is one away from the previous reference, and return an
    > error
    > message if it isn't).
    >
    > Thanks!




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1