Pop up alert on meeting a criteria

[Closed]
Report
Posts
1
Registration date
Friday August 6, 2010
Status
Member
Last seen
August 6, 2010
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,



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

3 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Could you please upload a sample EXCEL file WITH sample data, macro, formula , conditional formatting etc on some shared site like https://authentification.site , http://docs.google.com, http://wikisend.com/ , http://www.editgrid.com etc A N D post back here the link to allow better understanding of how it is now and how you foresee. Based on the sample book, could you re-explain your problem too
Hi rizvisa 1,

I have uploaded the excel file as required by you in

Download link : http://wikisend.com/download/967276/FOR UPLOADING.xls

forum link : [URL=http://wikisend.com/download/967276/FOR UPLOADING.xls]FOR UPLOADING.xls[/URL]

Here, i enter the values for row 12 and row 13 on a continuous basis. and row 14 is computed. i enter the data for row 16 and automatically row 19 is computed (as given in the sheet). Now i want a pop-up alert when E19 exceeds 50% or is lesser by 50% of the average of D19 and C19. Subsequently, when i enter data for FY12E in this example, i need to get a pop-up alert when F19 exceeds 50% of the average of E19 and D19.

I tried doing data validation as follows :
=OR(E19>(AVERAGE(C19:D19)+50%),E19<(AVERAGE(C19:D19)-50%)
However it does not work and i am trying to find out any macro for the same.

rgds
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
since this can happen by change in C12, C13, C16, D12, D13, D16,.so you need to put validation on these cells
hi rizvisa1,

i tried putting the data validation on these cells. However it does not work. Is there any macro which has to be written?

Rgds
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Just occurred to me. If you use validation, it will not allow you to enter those values. From your question it seems you just wants to see a warning. If that is so then you need a macro