Forum Discussion
Data Validation Formula to limit cell input range based on value in another cell
Select cell B1.
On the Data tab of the ribbon, click Data Validation.
Select Custom from the Allow dropdown.
Enter the following in the Formula box:
=OR(A1<>"LIMIT",AND(B1>=0,B1<=10))
If you wish, enter appropriate messages in the Input Message and Error Alert tabs.
Click OK.
Select cell B1.
On the Data tab of the ribbon, click Data Validation.
Select Custom from the Allow dropdown.
Enter the following in the Formula box:
=OR(A1<>"LIMIT",AND(B1>=0,B1<=10))
If you wish, enter appropriate messages in the Input Message and Error Alert tabs.
Click OK.
HansVogelaar Thank you very much.
I however do have my own request which in line with same condition but in my case, I want the same condition to apply to range of sales from I6 to P5002.
The condition is as follows:
If a value in Column F is "NEGATIVE", Cells I6 to P5002 can only take values less than 0 and a maximum value of -9999999999. Or Else any value greater than 0.
I tried to apply the same formulae but it requires a dynamic formulae range which I am failing to create in in the Data Validation "Custom" criterion.
I am just a Spreadsheet enthuast experimenting with Ms Excel to create some templates at work for decision makers
Select I6:P5002. I6 should be the active cell in the selection.
On the Data tab of the ribbon, click Data Validation.
Select Custom in the Allow drop-down.
Enter the following formula in the Formula box:
=OR(AND($F6="NEGATIVE", I6<0, I6>=-9999999999), AND($F6<>"NEGATIVE", I6>0))
HansVogelaar Thanks very much @HansVogelaar... You saved my new job.
GRATEFUL