Excel Data Validation Not Working

Question

Hi,

When I use data validation, it will pick up the number I entered if i type it manually in cells. But any number that comes from a formula/Math sum will not be picked up unless I click on the cell; I was wondering if their was a solution where the Data Validation will be picked up from autofil math equasions.

Answer 1

That won't work.

Let's say you have a formula in C2 that depends on A2 and B2. The values of A2 and B2 are entered by the user.

You'll have to create data validation rules for A2 and B2 instead of for C2.

Here is a simple example: C2 contains the formula =SUM(A2:B2).

You want the sum to be between 0 and 10.

Select A2 and B2.

On the Data tab of the ribbon, click Data Validation,

Select Custom from the Allow dropdown.

Enter the formula =AND($C2>=0,$C2<=10)

Activate the Error Alert tab and enter an appropriate error message.

Click OK.

Result: