+ Reply to Thread
Results 1 to 2 of 2

"Error" or "Disallow" Entries If Data Entered in Wrong Cell

  1. #1
    Registered User
    Join Date
    07-12-2017
    Location
    Texas
    MS-Off Ver
    Professional Plus 2010
    Posts
    1

    "Error" or "Disallow" Entries If Data Entered in Wrong Cell

    Hi, all. First time posting, not the first time reading these helpful forums, though. My first problem was coming up with a thread title, as my problem is difficult to explain... so here goes...

    I've created a simple spreadsheet that is to be used as a "log sheet" for my team. Let's call each new row entry a "case". New case entries are entered by rows, and the case's necessary information is entered under its appropriate column (such as dates, team member name, case #, etc.)

    Each new "case" will derive from one of two sources, let's call the first source "PLE" and the second source "SAL".

    So the first new entry will be made starting at cell A5, the next at A6 and so on...

    I've set up the following within the spreadsheet:
    1) A hidden tab titled "Lists"
    2) Data Validation/List for all Column A entries using a Drop Down (and sourcing from the "List").
    3) Conditional Formatting for all of Column A, so that if PLE is selected from the Drop-Down, the cell automatically formats with the background color yellow. If SAL is selected, the color fills in as green.

    So far, so good. Now, here's my issue, but before I go on, I shall explain: Because I'm barely beyond the beginner level of Excel proficiency, I knew of no other way - and was limited by screen/hardcopy printing space - to separate the PLE and SAL cases, so one spreadsheet was created at this time.

    So, further down the rows, are identical columns (for dates, data) for PLE and SAL cases. Each of the PLE data columns is formatted to the color yellow, and the SAL data columns are formatted green (just like Column A!) By the way, this was done by cell formatting only.

    HERE'S WHAT I WANT TO HAPPEN: I want a user to get an Error Message or somehow be disallowed to (mistakenly) enter data under the yellow/PLE data columns if he/she chose SAL under Column A.

    In a nutshell:

    * Row 5 - PLE case, selected in A5
    * Column G is a Date column, formatted yellow.
    * Column I is a Date column, formatted green.
    * If the user begins to enter data (for this PLE case) in I5, he/she should be disallowed, or get an error message, because, after all, Column I is for SAL cases.

    That's it. Don't know if the solution involved further Conditional Formatting for Formulas. That's why I turn to super-experts as yourselves.

    Whew!!! Hope that wasn't too difficult to understand. Seems like a lot of 'splainin' for what may turn out to be such an easy fix.

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: "Error" or "Disallow" Entries If Data Entered in Wrong Cell

    You will need VBA if you want to prevent/limit entry and produce an error message.

    You could check AFTER entry of the date and use CF to highlight the Date cell in error But it will still contain the entered date. You cannot clear the cell with a formula.

    Equally you could put an error message in a cell based on the same logic.


    And you cannot enter data in a cell AND have a formula in that cell.
    Last edited by JohnTopley; 07-12-2017 at 12:29 PM.

+ 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. Replies: 5
    Last Post: 02-05-2019, 12:03 AM
  2. Replies: 4
    Last Post: 06-11-2017, 02:03 PM
  3. [SOLVED] Column X-Ref list - Sheet1 Col A "pages", Col B:FL "Req" to Sheet2 ColA "req", ColB "page"
    By excel-card-pulled in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-07-2017, 09:30 AM
  4. Replies: 35
    Last Post: 01-13-2016, 02:16 AM
  5. [SOLVED] Formula Needed to fill multiple cells with "No" when the word "No" is entered into a cell
    By excelteam777 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-09-2013, 05:36 PM
  6. solution for the blank cell using the IF function
    By ragnaedge in forum Excel General
    Replies: 4
    Last Post: 08-23-2013, 11:03 AM
  7. [SOLVED] How to Count number of "Error" and "OK" after the word "Instrument" found in table row
    By eltonlaw in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-17-2012, 06:26 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