Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 16

Thread: Detect If Cell Contains A Formula Or Constant

  1. #1
    Join Date
    4th December 2007
    Posts
    6

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    8th December 2006
    Location
    New Zealand
    Posts
    3,923

    Re: Detect If Cell Contains A Formula Or Constant

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

  3. #3
    Join Date
    4th December 2007
    Posts
    6

    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!

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    28th November 2007
    Posts
    6

    Re: Detect If Cell Contains A Formula Or Constant

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

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    4th December 2007
    Posts
    6

    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!

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    12th September 2006
    Location
    The Great State of Texas
    Posts
    4,323

    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.
    MS MVP - Excel

  7. #7
    Join Date
    15th August 2005
    Location
    Mechelen, Belgium
    Posts
    6,042

    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.be ==> English articles ==> Excel memes

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

  8. #8
    Join Date
    8th December 2006
    Location
    New Zealand
    Posts
    3,923

    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.

  9. #9
    Join Date
    4th December 2007
    Posts
    6

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,714

    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.

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Use Cells For Array Formula Constant
    By Michael Avidan in forum EXCEL HELP
    Replies: 7
    Last Post: March 2nd, 2008, 00:40
  2. Replies: 10
    Last Post: January 5th, 2008, 01:00
  3. Replies: 2
    Last Post: October 5th, 2006, 21:33
  4. formula containing a constant
    By erniel2652 in forum EXCEL HELP
    Replies: 4
    Last Post: September 20th, 2005, 05:25
  5. Replies: 5
    Last Post: September 5th, 2005, 01:25

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
  •  
porno