Using data validation with AND / OR function for multiple criteria

mdimiller

New Member
Joined
Nov 5, 2013
Messages
22
Hello

I am trying to add data validation for a cell so that is either matches both criteria or the error message is displayed. The first criteria i have is that the amount entered in the cell (lets say I6) matches the amount already in another cell (E6), the second criteria required needs a bit of background info explained - when an amount is intially entered into E6 excell records the username and displays it in H6. In addition to this when the worksheet is opened the users username is displayed in N2. The second valiation is to ensure that when a user enters the value in I6 the user ID in H6 (recorded when E6 was completed) does not match that of the user id in M2. Individually they work fine with the below forumales in the custom data validation field:

For criteria 1: =(E6=I6)
For Criteria 2: =(H6<>$N$2)

But when i try and put them together with the AND function as =AND(E6=I6, H6<>$N$2), it only works if someone has entered the data in E6. If this is left as the default i.e 0 and no user id recorded in H6 it will let me add an amount that is not 0. This is the same if the cell is left blank with no default, so can't get around it that way. I have also tried using the Or function, but this hasn't worked either. Im hoping this is something and seeing as the function work fine independently im not sure why it isn;t working with the AND function

Any help would be appreciated

Thanks
 
IGNORE THE ABOVE

i know it would be something simple - just needed to select ignore blanks and seems to work.

Thanks
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top