Data Validation Formula to limit cell input range based on value in another cell

[CSPENNINE]

Hi,

This should be simple, but I cannot seem to find the same question asked before, and all solutions I have tried have failed.

If I have cell A1 and it contains any text (other than "LIMIT"), I do not want a limit to the value that can be input in cell B1.

If the text "LIMIT" appears in cell A1, the value that can be entered into cell B1 should be limited to 0-10(for example).

I know that you can use data validation to limit a cell input to between certain values, but I cannot work out how to apply this to a custom formula based on "LIMIT" appearing in cell A1.

Any help would be much appreciated.

Chris

[#2 · Accepted solution]

@CSPENNINE 

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.

[#3]

@HansVogelaar 

Thanks so much. I was trying to use "A1=" instead of "A1<>".

Chris

[#4]

@CSPENNINE 

Hi, 

I want to limit the number of responses from my custom list.

For example if i a have list which have Yes & No and i want to restrict the number of times where Yes drop down menu can be selected.

Can you please help.

Thanks in advance

[#5]

@Iceman123 

See the attached sample workbook. You are allowed to select 'Yes' at most 5 times.

There is VBA code in the worksheet module. Right-click the sheet tab and select 'View Code' to inspect the code, and to change the number of times 'Yes' can be selected, if you wish.

You'll have to allow macros when you open the workbook.

[#6]

@HansVogelaar Hi, Thank you soo much. You are totally a life saver. Really appreciate it.

God Speed.

[#7]

@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

[#8]

@BaZadok 

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))

[#9]

@HansVogelaar Thanks very much @HansVogelaar... You saved my new job.

GRATEFUL