Difficult formula question

[JRD-CFW]

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

 

[Mike H]

Hi,

You would need VB for that but you can have this in another cell

=IF(A1<>"",A1,SUM(A2:A10))

Mike

 

[Bob Umlas]

Ummmm, what??

 

[Sean Timmons]

If you don;t want the number to truly disappear, you can do:

Default format of A1 as Black
Default Format of A2:A10 as White

in cell A1:

conditional formatting
formula is:
=isnumber(A1)
format font as white

In cells A2:A10
formula is:
=isnumber(A1)
format font as Black.


=

 

[JRD-CFW]

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.

 

[Mike H]

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

 

[Sean Timmons]

VB is Visual Basic

Right-Click on your tab name, select View Code, and enter the wonderful
world of VB!

 

[JRD-CFW]

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?