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]

@Hans Vogelaar 

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

Chris