Is it possible to lock a subtotal?

[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

[Bryan Hessey]

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
---

[Mark@Work]

I agree with Bryan, any attempt to create the kind of "lock" you are describing will surely cause a circular reference.

Bryans solution just highlights "undesirable" totals in a differnt format.

Assuming your totals are in Column D, an alternative might be to display a message in column E; for example:

Cell E2 : IF(D2<>100,"Totals MUST be 100","")

Mark.

[Bryan Hessey]

I agree with Bryan, any attempt to create the kind of "lock" you are describing will surely cause a circular reference.

Bryans solution just highlights "undesirable" totals in a differnt format.

Assuming your totals are in Column D, an alternative might be to display a message in column E; for example:

Cell E2 : IF(D2<>100,"Totals MUST be 100","")

Mark.

Hi Mark,

Of course, there is always the obvious (as stated by me moons ago on another thread)

C = D - A - B

so what you put in for A & B determines C, and it always adds up to the 100 in D (C and D are Protected)

The drawback here is that C becomes a 'bucket' with not enough thought to it's contents, - I still prefer the employee who can 'front up' to the boss with bright red blotches on their budgets.


---