Quote Originally Posted by mattlazarus
My company is doing projections for 2007 and they want all our revenue streams to add up to, hypothetically, $100. Is there a way to lock $100 or less as the total, thus forcing people to subtract from column A before adding to column B?

Example:

A B C
10 20 70

To add 5 to column A, the user would need to subtract five from B or C.

I'd like this to be a stubborn lock (not just a polite TRUE/FALSE basic if statement).

Is the answer going to be excruciatingly complex?

Thanks,


M
Hi,

If you put on a 'Stubborn Lock' how will the user get to reduce one cell to increase another?

Select the cells that are to total 100, and Format, Conditional Format, select Formula, =AND(SUM(E$4:E$7)<>100,SUM(E$4:E$7)>0)
select your colour and OK

Use the range applicable to your case. (for E$3:E$7)

hth
---