Data validation - 3 cell addition should equal value in one cell

[aalim95]

Hi everyone,

This my first post ever in any forum. But could not resist to register in this forum as I have learned many things from here.

I am basically at beginners level. What I am trying to get achieved is:

I have a manually inputted value of "165" in H10. Then there are 3 more cells in the same row to the right of H10. Those cells are (I10, J10, K10). These three are a summation of a break up of the value in H10. I10 = 100, J10=30, K=35. These three cells need to be manual inputs as well.

So what I need to get done is - when someone is manually inputting these numbers the three cells (I10, J10, K10) should ALWAYS add up to the number inputted in H10. If there is a mistake in the input of the 3 cells then there should be some kind of a warning or popup that will tell the user that the three cells didn't add up to the value of H10. Or just give an error of some sort so that the person checks all the inputs again.

I think data validation can do this job. However since I am a noob, I can't get it to work.

Your help will be really appreciated.

For your ease I have attached the file as well.

[Harribone]

If you enter 165 in H10 when the other cells are blank and you then enter say 10 in one of the cells and press enter data validation would bring up a warning because the sum of the 3 cells mentioned would be 10 so far.

Maybe if you select cell H10 and click on Conditional Formatting>New Rule.
Click Use a formula to determine which cells to format.
Type in =H10<>I10+J10+K10.
Click format and choose your formatting options (say fill red and white text).

This will then highlight the cell when the other 3 cells added together do not sum up correctly.

[FDibbins]

I dont think DV will do this for you, because itt will be wanting a "valid" entry.

I think that Harribone's conditional formatting is the way to go with this, you can make all 4 cells turn bright red if the numbers dont balance

[aalim95]

Sorry I may sound like a noon. Is harribone's an add on or are you talking about the conditional formatting that's already there in my excel 2010? In any case how do I go about it? Some directional help would be awesome. Thanks so much!

[djapigo]

Conditional Formatting is standard... it's on the Home tab and about halfway down the ribbon...

As Harribone mentioned... click on Conditional Formatting, then New Rule... from Rule Type, choose the last item (Use a formula...)
Etc.

[FDibbins]

Assuming you want to apply CF to the range H7:K343...

1. highlight the range you want to apply the conditional formatting to
2. on the home tab, styles, select CF
3. select new rule, select use formula
4. enter =AND($H7<>"",$H7<>SUM($I7:$K7))

[aalim95]

Thank you so much guys! I never imagined I could access help in this manner. It's just awesome. I can't thank you enough.

I tried Harribone's way and it works. The drawback is the cell stays colored before entry so a whole column will stay colored which is probably not ideal.

Fddidins - thanks man. But I can't seem to get the formula to work. Can you please double check if the formula is right and let me know? I had highlighted all 4 cells H7 thru K7 and then chose conditional formatting and inserted your formula. then tried to do the manual entries. The result don't seem to kick in when I input inaccurate numbers not matching up to H7. Can you please help?

I really appreciate this kind of generous help. You guys are putting time to help me out I hope that's just generous!

[FDibbins]

I used that formula in the file you uploaded. It will only work if there is a value in the TOTAL column...
=AND($H7<>"",$H7<>SUM($I7:$K7))

See the attached

edit: I think the reason you couldnt get it to work was, from the looks of it, you only selected row 7 data - if you do that, it will only be applied to that range. You have to select the entire range you want to have it applied to

[aalim95]

Thanks a lot guys!

Okay now I have found the ideal solution to my problem that I had stated in my first post. Thanks to my talented younger brother.

This is what he came up with and it works PERFECTLY.

Note - he did use data validation.

You guys can see the attached file and input numbers so that the value of A1 is an addition of the values in B1, C1, D1. All of the values are manual inputs. So if the values input in B1, C1, D1, DO NOT add up the the value in A1 it gives you an error message. This is how he did it.

1. A1 = no formula
2. B1 = DATA VALIDATION > then input formula =SUM(B1) <= SUM(A1)
3. C1 = DATA VALIDATION > then input formula =SUM(B1:C1) <= SUM(A1)
4. D1 = DATA VALIDATION > then input formula =SUM(B1:D1)= SUM(A1)

Voila!

Now input any value in A1 and if you wrongly input numbers in B1, C1, D1 that does not add up to add to value in A1 it gives you an error!

Thanks guy and I hope this comes to help to someone as well like you guys helped me!





Copy of Teet.xlsx

[FDibbins]

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[aalim95]

Got it. I am not used to forums so thanks for teaching. I have marked the thread as solved and also added reputation to you and Harribone. Just not sure what reputation does to the user. Is it like a feedback system or something?

Thanks

[FDibbins]

If you look at the green dot/dots under a member's name, that gives you *some* indication of their "reputation" in the comunity here. Also, if you hover the mouse over those green dots/squares, it will show a comment from the forum server regarding that member.

So, happy to help and glad you got the answer you wanted...thanks for the feedback