Difficult formula question

  • Thread starter Thread starter JRD-CFW
  • Start date Start date
J

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

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

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

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


=
 
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.
 
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
 
VB is Visual Basic

Right-Click on your tab name, select View Code, and enter the wonderful
world of VB!
 
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?
 
Back
Top