Mike H, you are a genius. Thanks!

And I figured out how to make it recalculate the sum. Just "Delete" the sum
and it automatically recalculates it an enters in the new sum.

How do I find out more about VB so I can do it myself?

"Mike H" wrote:

Hi,

This is VB
Right click your sheet tab, view code and paste this in on the right then
try entering 1 in a1 and it does nothing. Delete the 1 and you get the sum of
a2 - a10 in a1.

I haven;t mafe it recalculate if you chnage a2 - a10 but that could be done

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Target.Address = "$A$1" Then
Application.EnableEvents = False
If IsEmpty(Target) Then
Target.Value = WorksheetFunction.Sum(Range("A2:a10"))
Else
If Target.Value = 1 Then
End If
End If
End If
Application.EnableEvents = True
End Sub

Mike

"JRD-CFW" wrote:

Thanks for the quick responses. I've been able to figure it would by using
another cell but I guess I was hoping for a cleaner way of doing it.

What it VB?

P.S. The word "displaces" in my original post should have been "displays".
Sorry for any confusion.

"JRD-CFW" wrote:

I would like to know whether there is a formula that does the following:

*when i number into A1 it displaces the number
*when i delete the number in A1 it displaces the sum of A2:A10

in other words, is the a formula that would underlie whatever value i enter
into A1 but then appear if i were to delete the value (but not the formula,
obviously) in A1

i thought i fought something that did that but i can't find it again.

Thanks.

JRD