Data validation based on multiple criteria

Question

I have a file that I would like to Data Validate.

My criteria will be:

• The name and office will need to be populated, if they enter their name and not their office, or office and not their name they will get a message to enter the missing information
• If they enter both their name and office, the cell will be locked/protected that their entry cannot be altered.

I hope this makes sense and can work. I have tried to write a custom formula in Data Validation, but keep getting the message that there is an error, but cannot figure out how to write it correctly.

Below is an example of the file I am hoping data validation can help me out.

Answer 1

Hi Suzzi-HFO,

I’m Jung and I’d be happy to help you out with your question.

Assuming the entries in question are all in column A and that the first cell to which you wish to apply the validation is cell A1, then, with the active cell somewhere in row 1, go to Name Manager (Formulas tab) and define:

Name: MyValidation

Refers to: =AND(LEN($A1)=8,1-ISERR(0+MID($A1,{1,2,4,5,7,8},1)),EXACT(MID($A1,6,1),"E"),ABS(77.5-CODE(MID($A1,3,1)))<13,COUNTIF($A:$A,$A1)=1)

After which you can apply data validation to cell A1 choosing 'Custom' in the Allow box and entering the following in the Formula box:

=MyValidation

Make sure you also uncheck the Ignore blank box.

A small point, but if you are intending to use the Circle Invalid Data tool to audit incorrect entries, just be aware that any cells to which you extend the data validation and which are blank will be circled as invalid. If this is not desirable I can provide you with an amended solution.

N.B. You didn't say, but I presumed that the two alphabetic characters both have to be upper case. The current validation disallows lower case.

Reference: https://techcommunity.microsoft.com/t5/excel/data-validation-multiple-criteria/m-p/1419126

You may check this links below for more details:

https://www.exceldemy.com/excel-data-validation-custom-multiple-criteria/
https://yacostasolutions.com/multiple-data-validation/
https://www.howtoexcel.org/11-awesome-examples-of-data-validation/

Note: This is a non-Microsoft website. The page appears to be providing accurate, safe information. Watch out for ads on the site that may advertise products frequently classified as a PUP (Potentially Unwanted Products). Thoroughly research any product advertised on the site before you decide to download and install it.

I hope this information helps. If you have any questions, please let me know and I’ll be glad to assist you further.

Best regards,
Jung

Answer 2

Hi Suzzi-HFO,

You may try to perform the steps mentioned above and please let us know the results by replying to this thread, and we will get back to you as soon as we can. We will further investigate it and update you once we able to find the best resolution on this one.

Best regards,
Jung

Answer 3

Thank you - before I try this what I will need is validation to validate the name/office combination for each session

• 1st session lines a6-b17
• 2nd session lines c6-d17
• 3rd session lines e6-e17
• 4th session lines a22-b33.......