+ Reply to Thread
Results 1 to 4 of 4

Create ERROR message when two columns in the same row are used

  1. #1
    Registered User
    Join Date
    03-26-2014
    Location
    Kippens, NL
    MS-Off Ver
    Excel 2003
    Posts
    2

    Create ERROR message when two columns in the same row are used

    Hi folks. I'm trying to create a situation where the user would get an error message when they tried to enter numbers in two specific columns in the same row. That is, in row C they can only enter a number ( 1 or 2) into C5, C6 or C7. If they enter a number into two of these cells, I want an error to appear. They would have to stop and correct the error.

    I've tried two scenarios with no luck:

    1. I've data validation where I did SUM(C5:C7) and hid the answer in C8. I then tried to get a data validation to display an error code when C8 exceed 2. This didn't work.

    Any ideas?

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Create ERROR message when two columns in the same row are used

    You would have to put the data validation in C5 C6 and C7 (NOT C8)

    Put this as the custom data validation for each of those 3 cells.
    =COUNT(C$5:C$7)<2

  3. #3
    Registered User
    Join Date
    03-26-2014
    Location
    Kippens, NL
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Create ERROR message when two columns in the same row are used - HELP AGAIN!!!

    Ok second problem...in G5, H5 and I5 there can only be one entry (either a 1 or a 2) - so the count function works. However, I also
    want a way to make sure that each entry is 1 or 2 or another error would appear (of course someone entered 3 and there was no error). I was using data validation max/min to do this. Any idea how I could get BOTH conditions in data validation. Could I use an IF statement, what would be the true value...the false value would be "Error, value must be 1 or 2".

    Also, is there a FAST way to get these data validations to each cell in a column, or must I do each cell in the column individually?

    Thanks

  4. #4
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Create ERROR message when two columns in the same row are used

    Try this (still entered in all 3 cells)

    =AND(COUNT(C$5:C$7)<2,MAX(C$5:C$7)=2,MIN(C$5:C$7=1))

+ 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. Need to Create Error Message Box during Runtime error 1004
    By ExcelHelp2013 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-14-2013, 06:24 AM
  2. [SOLVED] Create an error message
    By jonas245 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-27-2012, 09:14 PM
  3. [SOLVED] Create a error message for a duplicate entry
    By barbercabinet58 in forum Excel General
    Replies: 2
    Last Post: 11-07-2012, 11:30 AM
  4. Need to create a error message box
    By lostinformulas in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-12-2006, 11:40 AM
  5. How to create an error message
    By Siberian in forum Excel General
    Replies: 3
    Last Post: 06-26-2006, 02:40 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