How to make a spreadsheet idiot-proof

[Howard]

I have a spread sheet with a number of formulas in cells; for example:
cell A1 =SUM(B1+C1).
Sometimes it may be necessary for the user to enter a different number in
cell A1, which deletes the formula. I need some code (or something) that
will monitor cell A1, and if this cell is empty, it will replace the correct
formula back in the cell. This way, the user only has to remember that if a
cell (A1) doesn't work properly, they just delete what is in the cell, and
the formula will return. Any suggestions? Thanks

 

[Rick Rothstein]

What cells did you mean when you said "..number of formulas in cells"? Are
you talking about a range of contiguous cells (like in a column or in a row
or in a rectangular area)? If yes, are the formulas in this range of cells
identical except for the cells being referenced from within the formulas
(in other words, can the formula in one cell of the range be produced by
copying another cell from the range into it)? If yea, then will all the
cells in the range have formulas?

 

[Gord Dibben]

Under which condition(s) would it sometimes be necessary to overwrite the
formula in A1?

This would not be good practice in my opinion.

If user enters a number in A1, how does A1 become "empty"?

You don't have to use the value in A1 if you don't want.

Have user enter a number in another cell and use that for calculations.

BTW..............=B1 + C1...............no need for SUM


Gord Dibben MS Excel MVP

 

[Howard]

Yes, there are contiguous cells, ie. A1 thru A10 that would have the same
formula except for referenced cells. I now have the user copy a formula from
another cell, like A10, and paste it into cell A1. The only problem is that
I'm dealing with users who have no computer knowledge. My thought was that
if I applied script to these cells, and told the user that if the
computations were not right, just delete the contents of the cell, and the
script would reinsert the correct formula. I keep most of the complicated
formulas on sheet 2, and on sheet 1, I reference sheet 2. For example, A1
might equal sheet2!A1. I also have formulas which use =SUMIF to add
information from other columns. The user does sometimes have a need to enter
his own numbers in the (=SUMIF) cells, but then something changes, and the
formula needs to be replaced. I don't know, maybe I'm just barking up the
wrong tree.

 

[Howard]

Thanks for your reply. I tried to explain better in the reply to Rick
Rothstein, but I wanted to thank you for your reply also. I'm not sure I
explained everything the way I should. Thanks again.

 

[Gord Dibben]

Jacob Skaria posted this in another forum.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("A3")) Is Nothing Then
If Target = "" Then Target.Formula = "=A1+A2"
End If
Application.EnableEvents = True
End Sub

Adjust the ranges to suit.


Gord

 

[Howard]

Sounds like what I'm looking for. I'll give it a try. Thanks.