Data validation on a formulated cell.

[DeanRobinson]

Hi all id like to set up a validation rule on a cell that has the below formula, the problem i have is if i imput an invalid number the warning comes up but if i add anything in e16:cu16 that takes the number over it doesnt, is there a way round this??

=COUNTIF((E16:CU16),"V")

 

[Gerald Higgins]

Can you explain in more detail please what exactly you want to do ?

If you already have a formula in a cell, why would you want to use Data Validation on it ? Wouldn't any input to the cell - whether or not controlled by Data Validation - over-write the formula ?

 

[DeanRobinson]

In C16 i have the formula =COUNTIF((E16:CU16),"V")

what the user does is when someone books vacation they insert a V in e16:cu16 my formula in c just counts how many people are booked off on that day.

What i want the validation to do, is once c16 get to 10 people if you try and book someone else off you would get a messagebox: there are to many people off.

 

[Gerald Higgins]

Ah, I understand. Let me think about that.

 

[Gerald Higgins]

I'm not sure if you can do it directly, but you can do it indirectly.

For example, in some cell, let's say C17, you could have a formula like this

=if(C16>=10,"","V")

Then, for your data validation, specify LIST, and specify cell C17 as the source of the list.
Does this make sense ?
If you already have 10 or more "V"s, then the Data Validation list will be blank - you won't be able to input another "V".
If you have 9 or less, the Data Validation list should be "V", allowing you to input another.

If you choose this route, you might want to have another cell that just flags up a message, so that users know the maximum has been reached, for example

=if(C16=10,"Maximum people on vacation has been reached",if(C16>10,"Maximum people on vacation has been exceeded",""))

Bear in mind that Data Validation will not TOTALLY prevent users inputting another V. For example, they will be able to do it by Cutting and Pasting.