Data Validation

[VinceF]

Good Morning,

This should be an easy one for the experts....thanks in advance.

I believe this can be accomplished with Data Validation

Cell N3 contains a DDL with Blue, Blue/Wh, White
Based on the selection from cell N3 I'd like it to limit the maximum entry number in Cell H3
For example: If BLUE is selected the maximum number in cell H3 would be limited to 14.3, if Blue/Wh is selected max number allowed in H3 would be limited to 16.8

VinceF
Office 2016

 

[Anonymous1378]

Something along these lines?

EDIT: Oops, Switch may not be out for 2016, try a nested IF instead i.e. =IF(N3="Blue",14.3,IF(N3="Blue/WH",16.8,0))

 

[VinceF]

Thank you for the reply..
I had tried this formula IF(N3="BLUE",MAX(14.3)) but that gave me a "doesn't match validation restrictions" error message.

Note: H3 cell could contain a lower number than 14.3, I'd like to make it so that 14.3 is the highest number allowed.

Thanks

 

[Anonymous1378]

Thank you for the reply..
I had tried this formula IF(N3="BLUE",MAX(14.3)) but that gave me a "doesn't match validation restrictions" error message.

Note: H3 cell could contain a lower number than 14.3, I'd like to make it so that 14.3 is the highest number allowed.

Thanks

That box is already a maximum box. No need to MAX() anything. Just an IF functions which returns the highest values should suffice. If you want to manipulate minimums, do it in the minimum box.

Your formula needs to start with an "=" sign, in case it wasn't obvious to you.

 

[VinceF]

You are correct, if I use the "decimal" function in Data Validation I can easily limit the low and high numbers. However the maximum input allowed is based on the selection of cell N3.
For example: If BLUE is selected in cell N3 the maximum number in cell H3 would be limited to 14.3, if Blue/Wh is selected in cell N3 the maximum number allowed in H3 would be limited to 16.8

 

[VinceF]

Anonymous1378,
After working a bit with your suggestion "=IF(N3="Blue",14.3,IF(N3="Blue/WH",16.8,0))" it worked perfectly. Thank you for taking the time to solve my problem, very much appreciated.

VinceF