revert cell value if the sum does not satisfy a condition

**spawn3010** · 02-10-2011, 05:19 AM

Hi All,

I am completely new to macros and programming.
I request your kind help for a small task
I have cell A1, cell B1 . The value of cell C1=(A1+B1)
The value of C1 should be equal to 100 only.
If the user changes value of A1 or B1 which makes the value of C1 not equal to 100 ,then there could be a pop up saying this value should be 100 only and then the valuse of A1 and B1 should be reverted back to the original values.

Kindly help in this
Thanks in advance to all

Regards

**DonkeyOte** · 02-10-2011, 05:31 AM

I don't really see how that works logically.

If A1 is 20 and B1 is 80 how do you change either cell without the sum becoming something other than 100 at least temporarily ?

You can't enter two different values to A1:B1 simultaneously unless you copy and paste from elsewhere.

**johnjohns** · 02-10-2011, 05:37 AM

Yes. If you can accept a null in one of the cells, perhaps a data validation can help.

**spawn3010** · 02-10-2011, 05:44 AM

Hi ,

Sorry for not being very clear about this

The values in A1 and B1 will be manually entered .The data in prev years sheet has to be refreshed
e.g.
A B C
60 40 =(A1+B1)
If now while refreshing his data if the user enters A1 = 70, B1 = 40 then the value of C1 would be 110
Now he should get a msg saying total can be 100 only. and the valuse of A1 should revert back to 60, so that the user knows what was his last years value.

Please let me know if it can be done...

**DonkeyOte** · 02-10-2011, 05:49 AM

Originally Posted by spawn0310

60 40 =(A1+B1)

If now while refreshing his data if the user enters A1 = 70, B1 = 40 then the value of C1 would be 110

Now he should get a msg saying total can be 100 only. and the valuse of A1 should revert back to 60, so that the user knows what was his last years value.

I reiterate; logically - this makes no sense given the simple fact that if C1 is 100 you can not alter A1 & B1 ... C1 would become <> 100 and you would revert back - ergo A1 & B1 remain constant.

**spawn3010** · 02-10-2011, 06:16 AM

Thankyou very much for the help John.
Aprpeciate the help DonkeyOte...
Yes the cell value of A1 and B1 will remain the same if the cell value is not equal to 100
however any other changes in the value of A1 and B1 which will maintian the condition will be accepted.

JohnJohns : Can you enable this for the entire column A, B and C.
Also could you please tell me how you did it....can you share the macro code please.

Thanks

**DonkeyOte** · 02-10-2011, 06:49 AM

@spawn3010, my understanding from your posts thus far:

A1: 30

B1: 70

A1 & B1 are to be changed manually

A1 & B1 aggregated must remain = 100

If A1 & B1 aggregated <> 100 changes must be reversed

If the above is correct:

The only ways A1 & B1 can be altered manually in Excel whist satisfying the above conditions are:

a) Highlighting A1 & B1, typing 50 and whilst holding CTRL pressing Enter.

or

b) Highlighting A1 & B1, typing in say ={2,98} and whilst holding SHIFT + CTRL pressing Enter (ie entering an Array formula)

Any singular alteration to the constants in A1 / B1 would result in a balance other than 100.
Per your logic they would be immediately reversed.

**johnjohns** · 02-10-2011, 06:54 AM

You can copy A1 and B1 and paste to the below cells. I used no macros, but data validation. See the data validation.

**spawn3010** · 02-10-2011, 07:14 AM

I now got your point DonkeyOte.
I will get more details from the user who actually is going to use the file..
Thanks for informing..I actually did not think on that path....

Thanks JohnJohns.... I learnt a bit more of data validation from you ...

The thread can be closed Sir. Thanks for all the help provided..