Validation data

diegomuser

New Member
Joined
May 16, 2021
Messages
3
Office Version
  1. 2019
Platform
  1. MacOS
Good afternoon,

I have a problem validating or nesting 2 criteria in a custom data validation.

I have a worksheet with 3 columns


A-QUANTITY

B-PRODUCT CODE

C-STOCK AVAILABLE


In column A (QUANTITY) it has the custom data validation with the formula = C2> = 0 (in case it is fulfilled, it throws me an error of Insufficient Stock).

So far it works fine, the problem is that if another employee first enters the quantity (eg 200 units) and then the product code, the validation is not fulfilled and the available stock becomes a negative number.


Then I need another validation that tells the employee that if he first enters the quantity (column A) while Product code (column B) is empty, that it does not allow entering any data. It only allows you to enter the quantity, if the product code is previously entered.

The formula would be = B2 <> "" or also = NO (ISBLANK (B2)) ..... both are valid. With the error message "You must complete the product code first"


How do I nest these 2 criteria?


= C2> = 0 and = B2 <> "" or = NO (ISBLANK (B2))


Try leading Y and other variants but it doesn't work or gives syntax error.


Can you help me with the correct syntax or some macro that does the same?

Thank you!
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,790
Office Version
  1. 365
Platform
  1. Windows
Try using
Excel Formula:
=AND(C2>=0,B2<>"")
 

diegomuser

New Member
Joined
May 16, 2021
Messages
3
Office Version
  1. 2019
Platform
  1. MacOS
I found a solution, thanks!

=AND(COUNTA(A2:B2)=2,C2>=0)
 
Solution

Forum statistics

Threads
1,143,687
Messages
5,720,301
Members
422,275
Latest member
Maria95

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
Top