Calling all Excel Pros! Help!

Posted by - PAUL B - on January 08, 2002 3:56 PM

Hi Everyone,

I am trying to figure out how I can have a document accumulate totals only.

Here is the situation...
I have a master doc which is updated by a whole bunch of linked copies. People enter numbers on their copy and those values are added up my master doc. However, if someone deletes information, that information is also removed. I want the master doc to only add numbers, not subtract if some changes their value to 0 etc.

I hope you guys understand my rookie excel talk...
Thanks
-Paul

Posted by Montano on January 08, 2002 4:31 PM

Not entirely sure what you want.
What if users were prevented from changing cells in the source sheets to 0 or from deleting values?
Would that provide what you need?

Try the following (put it in the source sheet code module) :-

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Value = 0 Or Not IsNumeric(Target) _
Then Application.Undo
Application.EnableEvents = True
End Sub

Posted by Paul Johnson on January 08, 2002 5:21 PM

Another option is to use data validation, and not allow 0 as a valid entry.
If you send me an your e-mail - I will send you an example

Posted by Montano on January 08, 2002 5:35 PM

Yes, that's a much better(simpler) way of doing it.
Be aware, however, that it is easy for a user to change the data validation setting.