Pop up alert on meeting a criteria

Closed
treeline
Posts
1
Registration date
Friday August 6, 2010
Status
Member
Last seen
August 6, 2010
- Aug 6, 2010 at 07:55 AM
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
- Aug 7, 2010 at 07:18 PM
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

rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
Aug 6, 2010 at 11:07 AM
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
0
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
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
Aug 7, 2010 at 05:21 AM
since this can happen by change in C12, C13, C16, D12, D13, D16,.so you need to put validation on these cells
0
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
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
Aug 7, 2010 at 07:18 PM
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
0