Data Validation - force entries to equal another cell

[durden3eb]

First, sorry if this has been asked before. I used the Search function and didn't find this in five pages, so hopefully it hasn't been asked recently at least.

I need to limit the entries in three cells to sum up to the total in another cell. For example, B1+B2+B3 must equal A1. I'm trying to use data validation to do this, by using a custom validation: =sum($B$1:$B$3)=$A$1. However, that isn't working ... I am allowed to enter a value in B1, but then cannot enter anything in B2 unless it is the remainder needed to sum up to A1. In other words, that validation is ignoring that users will still be allowed to enter a number in B3.

The total must equal A1, also. I don't want to give users the option to be less than A1 at all. Any suggestions?

 

[Lewiy]

The problem you will have here, as you have already discovered, is that during the process of entering the values in B1:B3, the total will at some point not be equal to A1 when you haven’t finished entering the values.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Might I suggest you just use some conditional formatting to highlight if the rules have been broken rather than trying to restrict the cell values themselves. Although, without knowing why you want to do this, I don’t know if that is a suitable solution.

 

[durden3eb]

That is an easier solution, but I'm creating a spreadsheet that our sales team will use, so I want to make it as limited as possible. What this will do is allow them to look at sales total from 3 different products, which obviously will add to some total, or A1 in my previous example. Then, cells B1:B3 can be manipulated to redistribute that total among those three products. So if before:

Product 1 - $25
Product 2 - $50
Product 3 - $75
Total - $150

I want to allow them to redistribute that $150 among the 3 products as they see fit, but the end result MUST always be $150.

Maybe data validation isn't the way to do that?