Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I have the result of a formula display in a blank cell
I would like to use a conditional statement and based on the condition move
the result of a formula to a blank cell. Example: cells A1 , B1 , C1 are blank. If a person enters data in A1 a math fuction is done and results are displayed in B1 and C1. However if the data is entered in C1 a different math fuction is done and the results are displayed A1 and B1. Is this possible? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I have the result of a formula display in a blank cell
And if the user enters data in B1, or any two of A1, B1 & C1?
-- macropod [MVP - Microsoft Word] ------------------------- "Snake007" wrote in message ... I would like to use a conditional statement and based on the condition move the result of a formula to a blank cell. Example: cells A1 , B1 , C1 are blank. If a person enters data in A1 a math fuction is done and results are displayed in B1 and C1. However if the data is entered in C1 a different math fuction is done and the results are displayed A1 and B1. Is this possible? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I have the result of a formula display in a blank cell
The conditional statement will produce an error if data is entered into more
than one of the 3 cells. The user will be able to enter data into any one of the 3 and get results in the other 2. Keep in mind that I am using this an an exsample, the actual sheet will be a lot more than 3 cells. "macropod" wrote: And if the user enters data in B1, or any two of A1, B1 & C1? -- macropod [MVP - Microsoft Word] ------------------------- "Snake007" wrote in message ... I would like to use a conditional statement and based on the condition move the result of a formula to a blank cell. Example: cells A1 , B1 , C1 are blank. If a person enters data in A1 a math fuction is done and results are displayed in B1 and C1. However if the data is entered in C1 a different math fuction is done and the results are displayed A1 and B1. Is this possible? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I have the result of a formula display in a blank cell
Hi Snake,
You can do this sort of thing using a 'Worksheet_SelectionChange' macro, which you place in the relevant worksheet's object. To do this, open the VBA Editor's Project Explorer window (eg Alt-F11), then double-click the entry for the worksheet you're using. Insert following lines in the code window: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Static oCell As Range If oCell Is Nothing Then Set oCell = ActiveCell If Intersect(oCell, ActiveSheet.Range("A2:C2")) Is Nothing Then GoTo Reset With ActiveSheet Select Case oCell Case .Range("A2") .Range("B2").Value = .Range("A2").Value * 1.5 .Range("C2").Value = .Range("A2").Value * 2.5 Case .Range("B2") .Range("A2").Value = .Range("B2").Value / 1.5 .Range("C2").Value = .Range("B2").Value / 1.5 * 2.5 Case .Range("C2") .Range("A2").Value = .Range("C2").Value / 2.5 .Range("B2").Value = .Range("C2").Value / 2.5 * 1.5 End Select End With Reset: Set oCell = ActiveCell End Sub The above procedure reacts to changes in A2:C2 according to whichever cell in that range was the last updated. It's a fairly trivial example, just to give you an idea of where to start. If you change the value in any one of these three cells, the other two get updated. The formulae in the code ensure the same mathematical relatioinship is maintained across all three cells. Note: In the above macro, €˜Target, although required for a €˜SelectionChange event, isnt used since it would prevent the target cells updating if the value in one of them is changed and a cell outside the target range is immediately activated without pressing the <Enter key. Cheers -- macropod [MVP - Microsoft Word] ------------------------- "Snake007" wrote in message ... The conditional statement will produce an error if data is entered into more than one of the 3 cells. The user will be able to enter data into any one of the 3 and get results in the other 2. Keep in mind that I am using this an an exsample, the actual sheet will be a lot more than 3 cells. "macropod" wrote: And if the user enters data in B1, or any two of A1, B1 & C1? -- macropod [MVP - Microsoft Word] ------------------------- "Snake007" wrote in message ... I would like to use a conditional statement and based on the condition move the result of a formula to a blank cell. Example: cells A1 , B1 , C1 are blank. If a person enters data in A1 a math fuction is done and results are displayed in B1 and C1. However if the data is entered in C1 a different math fuction is done and the results are displayed A1 and B1. Is this possible? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If formula result = 0, do not display-leave cell blank | Excel Worksheet Functions | |||
Average Formula to display blank cell if named range is blank | Excel Worksheet Functions | |||
How to get Blank into a cell as a result of a formula | Excel Worksheet Functions | |||
Display result in same cell as formula | Excel Discussion (Misc queries) | |||
How to get a formula result zero as blank cell | Excel Discussion (Misc queries) |