Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Lawrence, Kansas, USA
    Posts
    202
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Identifying formulas (Excel 2000)

    I need to identify whether a cell contains a numeric constant or a function which returns a number. For example I want to distinguish between a cell that has 3 entered as a constant and a cell that has A1+B2 whose result is 3.

    The functions (formulas) vary. They don't all contain +,-,/ or *. But I think they do all contain at least one of these operators.

    Any help would be much appreciated.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Identifying formulas (Excel 2000)

    If you just want to select the cells with formulas, you can press F5 (or Ctrl-G), click on special, and then select Formulas. If you need to, you can further refine the selection by selecting Numeric, Text etc. When you have selected your requirements, click OK, and all the relevant cells should be selected.

    Andrew C

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

    Re: Identifying formulas (Excel 2000)

    Select all of the cells that you want to identify. Then from the Edit menu select "Go To". In the dialog box click on the "Special..." button. In this dialog box select either "Constants" or "Formula" depending on which you want to identify. click on OK, and the cells should be selected.
    Legare Coleman

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

    Re: Identifying formulas (Excel 2000)

    In a variation on <!post=Post 113007, 113007>Post 113007<!/post>, you could write a user defined function in a module:

    Function IsNumericFormula(aCell As Range) As Boolean
    IsNumericFormula = aCell.HasFormula And IsNumeric(aCell)
    End Function

    Then you can put a formula in a cell:

    =IsNumericFormula(A5)

  5. #5
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Lawrence, Kansas, USA
    Posts
    202
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Identifying formulas (Excel 2000)

    Thanks for all of your quick responses. I would like to use conditional formatting to show the difference between the cells easily, so the user defined formula seems to be the way to go.

    However, I'm not quite sure how to set it up in the conditional formatting dialogue box. It seems that the the argument in the function would be nothing, but that doesn't work.

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

    Re: Identifying formulas (Excel 2000)

    Try this (based on the post I referred to earlier):

    Function IsNumericFormula() As Boolean
    IsNumericFormula = (Application.Caller.HasFormula) And IsNumeric(Application.Caller)
    End Function

    Now, in the conditional formatting dialog box, select Is Formula and in the condition box, enter
    =IsNumericFormula()

  7. #7
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Lawrence, Kansas, USA
    Posts
    202
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Identifying formulas (Excel 2000)

    Works great. Thanks.

Posting Permissions

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