Forum Discussion
Help in data validation formula: Cell cannot equal more than another
Without going down the route of VBA and change events, you could do this with conditional formatting which will not give a message but can allow a message which is not usually visible to be displayed under certain conditions i.e. if cell H23 > H2.........see attached file (I added the formula in H23 adding A19:A22 to demonstrate - to see how it works, change cell A20 from 1000 to zero).
The formula seems to work, unless i'm missing some additional criteria you're looking for - See image...
The formula simply checks whether the sum of A19:A22 is greater than the number in H2 - If it is then the result will be the value in H2, if not it takes the sum of A19:A22.
Let me know if I have misinterpreted.
Oh I see, I will give more clarification, thanks for checking. : )
A19:A22 can equal more than H2 together, and their actual amount would show in H23.
I need, through data validation, a warning message to appear when A23 is exceeding H23, that gives us the option to choose accepting the overage or changing it. There are rare circumstances that we allow A23 to surpass H2's value.
Does that help for clarification? If not, just let me know again and I will do my best to explain.
Thank you
Without going down the route of VBA and change events, you could do this with conditional formatting which will not give a message but can allow a message which is not usually visible to be displayed under certain conditions i.e. if cell H23 > H2.........see attached file (I added the formula in H23 adding A19:A22 to demonstrate - to see how it works, change cell A20 from 1000 to zero).