+ Reply to Thread
Results 1 to 11 of 11

Prevent Excel User from Leaving a Cell Blank When Adjacent Cell is Greater Than Zero

  1. #1
    Registered User
    Join Date
    04-27-2017
    Location
    Masschusetts
    MS-Off Ver
    2010
    Posts
    73

    Prevent Excel User from Leaving a Cell Blank When Adjacent Cell is Greater Than Zero

    Good afternoon,
    I'm trying to prevent excel users from leaving the cells column Q empty when the cells in column P's value are greater than zero. Column P is an automatic calculation and locked from the user. Can you shed some light on how to do this (not using VBA). Thanks for your help.
    Regards,
    Saabra

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Prevent Excel User from Leaving a Cell Blank When Adjacent Cell is Greater Than Zero

    Use Conditional formatting.

    A: Select all cells by clicking on the top left of Excel

    Select the Home Tab then conditional formatting.

    Select use formula and type this in the formula box =and($P1="",$Q1<>"") set the format to red fill

    B: Select all cells in column P by clicking on P at the top of excel

    Select the Home Tab then conditional formatting

    Select use formula and type this in the formula box =and($P1="",$Q1<>"") set the format to Yellow fill
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Registered User
    Join Date
    04-27-2017
    Location
    Masschusetts
    MS-Off Ver
    2010
    Posts
    73

    Re: Prevent Excel User from Leaving a Cell Blank When Adjacent Cell is Greater Than Zero

    Thank you mehmetcik. I'll give it a try.

  4. #4
    Registered User
    Join Date
    04-27-2017
    Location
    Masschusetts
    MS-Off Ver
    2010
    Posts
    73

    Re: Prevent Excel User from Leaving a Cell Blank When Adjacent Cell is Greater Than Zero

    Hi mehmetcik,
    I tried A and B and neither worked. Thank you for your suggestion. Any other ideas?
    Saabra

  5. #5
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Prevent Excel User from Leaving a Cell Blank When Adjacent Cell is Greater Than Zero

    Sample attached
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    04-27-2017
    Location
    Masschusetts
    MS-Off Ver
    2010
    Posts
    73

    Re: Prevent Excel User from Leaving a Cell Blank When Adjacent Cell is Greater Than Zero

    Thanks again mehmetcik. The attachment you provided doesn't quite fit what I'm looking for. Can I send you the spreadsheet?
    Saabra

  7. #7
    Registered User
    Join Date
    04-27-2017
    Location
    Masschusetts
    MS-Off Ver
    2010
    Posts
    73

    Re: Prevent Excel User from Leaving a Cell Blank When Adjacent Cell is Greater Than Zero

    Hi again,
    If you can see the attached, I'm trying to get all the columns that immediately follow the shaded columns to provide the alarm. The shaded columns are D, P, AB, AN, and AZ. The columns that need the alarm are E, Q, AC, AO, and BA. Can you see the attached?
    Thanks,
    Saabra
    Attached Files Attached Files

  8. #8
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Prevent Excel User from Leaving a Cell Blank When Adjacent Cell is Greater Than Zero

    Ok. Try the sample
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    04-27-2017
    Location
    Masschusetts
    MS-Off Ver
    2010
    Posts
    73

    Re: Prevent Excel User from Leaving a Cell Blank When Adjacent Cell is Greater Than Zero

    Mehmetcik,
    The solution you provided would help users see when they've entered in data accidentally, but it doesn't achieve the desired goal. If I may describe what needs to happen.

    At the beginning of a clinic day, users know exactly how many appointments were scheduled for clinician A. Let's say it's five. The next column asks them to enter how many appointments were kept/held. Let's say three out of 5 appointments were kept. The shaded column then automatically calculates how many appointments were missed. In this example it's 2. Then the spreadsheet is asking them reveal how many of the two were missed because the clinician failed to show. So the number in the shaded column always has to be greater than zero to require users to respond. Typically, they leave this blank and they shouldn't. Doesn't this description help?
    Saabra

  10. #10
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Prevent Excel User from Leaving a Cell Blank When Adjacent Cell is Greater Than Zero

    Try changing the conditional format formula to =AND(D1>0,E1<>"")

  11. #11
    Registered User
    Join Date
    04-27-2017
    Location
    Masschusetts
    MS-Off Ver
    2010
    Posts
    73

    Re: Prevent Excel User from Leaving a Cell Blank When Adjacent Cell is Greater Than Zero

    Mehmetcik, I tried changing the formula to what you suggested, but no result. Thanks for your effort. I'll have to let go of this one.
    Regards,
    Saabra

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Prevent User from leaving form without a password
    By ccpsc in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-13-2016, 03:48 PM
  2. Leaving a cell blank in Excel until the data is entered
    By gelert64 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-14-2015, 04:06 PM
  3. [SOLVED] Force data entry in cell with drop down list (i.e. Prevent user from leaving cell blank)
    By moosetales in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-15-2014, 05:28 AM
  4. [SOLVED] Prevent user from closing worksheet if cell is blank
    By maryren in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-15-2013, 12:11 PM
  5. [SOLVED] Prevent User in Selecting a Month that is Greater Than What is in Cell A1
    By Hudas in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 04-13-2012, 02:57 PM
  6. User form to display adjacent cell value and move to next blank cell
    By Ketel in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-21-2008, 03:15 PM
  7. [SOLVED] prevent a user leaving a blank cell in excel2003
    By Ian Varty in forum Excel General
    Replies: 1
    Last Post: 04-15-2005, 09:06 AM

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