Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Difficult formula question
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Difficult formula question
Hi,
You would need VB for that but you can have this in another cell =IF(A1<"",A1,SUM(A2:A10)) Mike "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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Difficult formula question
Ummmm, what??
"JRD-CFW" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Difficult formula question
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" 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Difficult formula question
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Difficult formula question
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Difficult formula question
VB is Visual Basic
Right-Click on your tab name, select View Code, and enter the wonderful world of VB! "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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Difficult formula question
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Difficult ------------- question | Excel Discussion (Misc queries) | |||
difficult question | Excel Discussion (Misc queries) | |||
Difficult (at least to me) formula question | Excel Worksheet Functions | |||
Very simple, but difficult formula question | Excel Worksheet Functions | |||
Difficult Excel Question | Excel Discussion (Misc queries) |