Announcement

Collapse
No announcement yet.

Detect If Cell Contains A Formula Or Constant

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Detect If Cell Contains A Formula Or Constant

    I need an equation that lets me determine whether a cell contains a static constant value (either a string or a number), or an equation that generates a value.

    Problem: I have a cell that contains a default equation that generates a default value, depending on a few conditions. The user can overwrite this cell with a constant. In another cell I need to know whether that cell is still the default equation or a new constant value.

    I cannot simply test the value of the contents to see if they match my default, because the user may choose to enter that value.

    Any help you can provide would be appreciated! Thanks in advance.

  • #2
    Re: Detect If Cell Contains A Formula Or Constant

    With vba you can use the hasformula property. Does that help?
    Reafidy

    Forum Rules | Anatomy Of A Good Thread Title | What Have You Tried So Far? | Are You A Cross Poster?

    Comment


    • #3
      Re: Detect If Cell Contains A Formula Or Constant

      Unfortunately I cannot use VBA in this application because of my client's very restrictive security rules. Is there a way to get access to the same information as the "hasformula" property but with an equation?

      Thanks!

      Comment


      • #4
        Re: Detect If Cell Contains A Formula Or Constant

        You could go to "Edit > Go To > Special" and select formulas, constants, etc.

        Comment


        • #5
          Re: Detect If Cell Contains A Formula Or Constant

          But as I said I need to use this information in an IF statement in the formula of another cell. Any other ideas? Thanks!

          Comment


          • #6
            Re: Detect If Cell Contains A Formula Or Constant

            I don't know of a way to tell via a worksheet function whether a cell contains a formula.
            Entia non sunt multiplicanda sine necessitate.

            Comment


            • #7
              Re: Detect If Cell Contains A Formula Or Constant

              I just checked it, and it turns out that you can't check wheter the formula begins with an =

              e.g.

              =4+6

              yields 1 with =LEFT(thatcell;1)

              so this:

              =(LEFT(A1;1)="=")

              won't help us either.

              Wigi
              Regards,

              Wigi

              Excel MVP 2011-2014

              For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

              -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

              Comment


              • #8
                Re: Detect If Cell Contains A Formula Or Constant

                The user can overwrite this cell with a constant.
                I would suggest finding an alternative the above situation. Eg a helper column where a user can insert a value then the formula will check if this helper column is blank and display the formula or constant if its not blank.

                Like SHG I dont know of a "has formula" formula or equivalent.
                Reafidy

                Forum Rules | Anatomy Of A Good Thread Title | What Have You Tried So Far? | Are You A Cross Poster?

                Comment


                • #9
                  Re: Detect If Cell Contains A Formula Or Constant

                  Right, eveything you try to do with the cell accesses the VALUE of the cell, not its formula. The closest I could find is the cell() function, but that will not work either. It can tell you if the value is formatted as a date or a percentage, but not if the cell contains a formula or a constant. Same with IsNumber(); it just tells you whether the VALUE of the cell is a number.

                  Comment


                  • #10
                    Re: Detect If Cell Contains A Formula Or Constant

                    Detect If Cell Contains A Formula Or Constant
                    That would ANY cell with ANY data.

                    Formulae or Function cannot delete cells.

                    Comment


                    • #11
                      Re: Detect If Cell Contains A Formula Or Constant

                      Originally posted by misterspike
                      Right, eveything you try to do with the cell accesses the VALUE of the cell, not its formula. The closest I could find is the cell() function, but that will not work either. It can tell you if the value is formatted as a date or a percentage, but not if the cell contains a formula or a constant. Same with IsNumber(); it just tells you whether the VALUE of the cell is a number.
                      I think you have a mindset towards your problem. Can you explain why my suggestion wont work.
                      Reafidy

                      Forum Rules | Anatomy Of A Good Thread Title | What Have You Tried So Far? | Are You A Cross Poster?

                      Comment


                      • #12
                        Re: Detect If Cell Contains A Formula Or Constant

                        ISNUMBER will distinguish between a default '3 and a users 3.

                        Comment


                        • #13
                          Re: Detect If Cell Contains A Formula Or Constant

                          This is going waaaay off the Thread Title.

                          misterspike, I suggest you start a new thread and actually READ the BIG BOLD text on the New Thread pagfe so you are NOT assumming your means to an end.

                          Comment


                          • #14
                            Re: Detect If Cell Contains A Formula Or Constant

                            Dave,

                            I am reopening this due to a PM from the OP which gets the thread back on track. Hope that is OK.

                            You can use an old excel 4 Macro. (cant be called directly from worksheet)

                            1) Go to Insert - Name - Define.
                            2) Add a name (CellHasFormula, or IsFormuala etc)
                            3) In the refers to box, type:

                            =GET.CELL(48,OFFSET(INDIRECT("RC",FALSE),0,-1))

                            Enter into a cell =HasFormula it will return true or false depending on wether the cell to the left has a formula or not. You can change the offset to reflect any cell.

                            If you want to use conditional formatting, use this formula:

                            =GET.CELL(48,INDIRECT("RC",FALSE))
                            Reafidy

                            Forum Rules | Anatomy Of A Good Thread Title | What Have You Tried So Far? | Are You A Cross Poster?

                            Comment


                            • #15
                              Re: Detect If Cell Contains A Formula Or Constant

                              Nice one, Reafidy
                              Entia non sunt multiplicanda sine necessitate.

                              Comment

                              Working...
                              X