Data Validation for Cell having formula

[padmajadrao]

hi,


I have a cell which has a formula. I need to validate the formula result such that if a user inputs a number in the formula source cell and if the formula result is 10% more or less than the average of previous two cells a message box appears saying that it is an incorrect entry.


For eg : E10 has formula ((E5-E3)/E5)where E5 and E3 are two numbers. and if E10 is 10%greater or 10% lesser than average of D10 and C10, message box has to pop up when i make an entry in E5 or E3 which does not validate the condition. Here the cells D10 and C10 are computed similar to E10. Further this validation has to work for this entire row.


Thanks in advance

 

[Hui]

Padmajadrao

Try applying the following Data Validation using a Custom Formula to both E3 and E5


=IF(AND(E10>(AVERAGE(C10:D10)*0.9),E10<(AVERAGE(C10:D10)*1.1)),TRUE,FALSE)

 

[padmajadrao]

Hi,


Thank you for the reply. I have already tried doing this. This does not work since the entries in E5 and E3 will be done subsequent to the formula entry in E10 (Since its a financial projection sheet). I was just wondering is there any other way of doing this like writing in VBA?