+ Reply to Thread
Results 1 to 3 of 3

Data validation (multiple criteria)

  1. #1
    Registered User
    Join Date
    04-28-2020
    Location
    Montreal
    MS-Off Ver
    365 Home Edition
    Posts
    3

    Data validation (multiple criteria)

    I am trying to use data validation based on multiple criteria.

    Please refer to the attached sample file. In Sheet1 I have two columns: File# and Date closed. If the latter is missing, that means that the file is being processed by an agent. In Sheet2, I have the files that are on hold for whatever reason. There are four columns in this sheet: File#, Date (date it was put on hold), Deadline and Resume (date when work was resumed).

    Agents only have access to Sheet2.

    Now, here is the challenge: an agent can indicate the file# he wants to put on hold as long as :

    - the file# is valid (number is present on Sheet1, column File#). An agent should not be able to write 1118 in the File# column, as the file doesn't exist
    - the file is not closed (no date present on Sheet1, column Date Closed). For instance, the agent cannot write 1113, even if the file exists, as it is already closed (can't put on hold a closed file)
    - the file# is not already on hold (which means no duplicate is allowed as long as the file is on hold). However, duplicate is allowed if the file was previously on hold but the work has resumed at a given moment in the past (an entry exists for that particular number, but a date is present in the column Resumed). For example, the agent should not be able to put on hold file 1115 as it is already on hold. However, he can put on hold file 1112, as it is still open and was on hold before (but the work has resumed since)

    For the first part, I used a simple data validation (list, range, no in-cell drop down). However, since multiple criteria is involved (including lists), I admit that this is beyond my comprehension and abilities. Any help would be greatly appreciated. Thanks!
    Attached Files Attached Files

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Data validation (multiple criteria)

    one option, if I've understood

    B4:custom rule of
    =(VLOOKUP($B4,Sheet1!$B:$C,2,0)/(1-(IFERROR(VLOOKUP($B4,$B$3:$E3,4,0),1)=0)))=0

    apply to other cells in B

  3. #3
    Registered User
    Join Date
    04-28-2020
    Location
    Montreal
    MS-Off Ver
    365 Home Edition
    Posts
    3

    Re: Data validation (multiple criteria)

    Excelent! So far it works great! I also took the time to understand the frmula and learn from it. Thanks!

+ 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. [SOLVED] Data Validation of Multiple Criteria with VBA
    By perpectuals in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-06-2018, 10:48 PM
  2. [SOLVED] Data Validation with multiple criteria
    By bdouglas1011 in forum Excel General
    Replies: 0
    Last Post: 09-23-2014, 05:24 PM
  3. Data validation with multiple criteria
    By mryan1962 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-15-2013, 05:56 PM
  4. Data Validation with Multiple criteria
    By glawson in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-04-2013, 09:57 AM
  5. [SOLVED] data validation WITH MULTIPLE CRITERIA...need help!!!!!!
    By Jhon Mustofa in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-30-2013, 01:15 PM
  6. Replies: 0
    Last Post: 12-12-2011, 07:27 PM
  7. Multiple criteria in Data Validation
    By Amy Stein in forum Excel General
    Replies: 2
    Last Post: 04-06-2006, 01:10 PM

Tags for this Thread

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