+ Reply to Thread
Results 1 to 7 of 7

Display a warning when a cell with a formula reaches a certain number

  1. #1
    Registered User
    Join Date
    01-27-2009
    Location
    Owen Sound, Ontario
    MS-Off Ver
    Excel 2007
    Posts
    55

    Display a warning when a cell with a formula reaches a certain number

    I am not even sure if there is a way to do this but here goes. I am creating a registration form for a conference we are having. I will not be the person filling this form out. For the first day we have a limit on the number that can attend. At the moment I have the spreadsheet calculating the number attending the conference according to the day(s) they choose whcih is basically a simmple caculation of adding choice 1 (which is the first day) and choice 2 (which is both days). I have this located right at the top of the form and highlighted so the individual taking registration will see. I tried to use data validation to create a senerio where the cell would create a message saying the limit had been reached once 60 was met but it does not work because the cell is automatically increasing and as far as I can tell what I tried only works if you are actually typing in the cell.

    Does anyone know of a way that I could do this while still keeping my formula in the cell? I am attaching a small sample of the spreadsheet. Cell F3 is the cell I am working with.

    Thanks for any assistance you might be able to give.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor JeanRage's Avatar
    Join Date
    03-02-2009
    Location
    Nice, France
    MS-Off Ver
    Excel 2003
    Posts
    705

    Re: Display a warning when a cell with a formula reaches a certain number

    Hi,

    You could try :
    Please Login or Register  to view this content.
    HTH

  3. #3
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: Display a warning when a cell with a formula reaches a certain number

    Is this enough?
    Attached Files Attached Files
    Last edited by Marcol; 03-30-2010 at 10:30 AM.

  4. #4
    Forum Expert
    Join Date
    09-09-2005
    Location
    England
    MS-Off Ver
    2007
    Posts
    1,500

    Re: Display a warning when a cell with a formula reaches a certain number

    well you could right some vba code, althernatively in G3 put =IF(F3>60,"Day one limit reached","")
    and you could conditionally format the whole sheet to turn it another colour using a formula to evalue with =$f$3>60 as the condition and choose a colour to catch their attention

    regards

    Dav

  5. #5
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Excel for Windows & Mac - all versions.
    Posts
    26,704

    Re: Display a warning when a cell with a formula reaches a certain number

    Hi,

    Put your limit of 60 in a cell somewhere and name it 'limit'
    Name your F3 cell 'Total'
    Put the formula
    Please Login or Register  to view this content.
    in F3

    Now go to the Worksheet_SelectionChange event in the VB Environment, (right click on the sheet1 tab name and View Code, then pick the Worksheet option from the left hand drop down in the big code window on the right), and enter the following code.

    Please Login or Register  to view this content.
    HTH
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  6. #6
    Registered User
    Join Date
    01-27-2009
    Location
    Owen Sound, Ontario
    MS-Off Ver
    Excel 2007
    Posts
    55

    Re: Display a warning when a cell with a formula reaches a certain number

    I'm not very familiar with code. Where would I put this code? I would love to try it.

  7. #7
    Valued Forum Contributor JeanRage's Avatar
    Join Date
    03-02-2009
    Location
    Nice, France
    MS-Off Ver
    Excel 2003
    Posts
    705

    Re: Display a warning when a cell with a formula reaches a certain number

    Hi,

    Point the Sheet tab name
    Right click
    select view code
    and copy code ...
    that's it ...

    HTH

+ 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