| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
keep previous result when change, urgent
I made a validation list in A1 for different scenarios, I could choose 3
different secnarios from this drop-down list. each time, when I choose one scenario, there is a result in B2, for instance if I choose scenario1 in A1, b1=100; choose scenario2,b1=200; choose scenario3, b1=300. The question is, Can I make c1, c2 and c3 store b1 value based on different choosen scenario? For example, I choose scenario1 in drop-down list in A1, b1 show equal to 100, c1 store a result 100; if I change my choice in drop-down list to scenario2, b1 will be 200, but c1 will still keep 100 result, at the same time c2 will be 200. I tried Iteration function in Excel sheet, but it doesn't work, I THINK vba might be the only choice. could someone help me out? Thanks in advance. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
keep previous result when change, urgent
Use the Change event in xl2000 and later
See Chip Pearson's page on events: http://www.cpearson.com/excel/events.htm -- Regards, Tom Ogilvy "jiang" wrote in message ... I made a validation list in A1 for different scenarios, I could choose 3 different secnarios from this drop-down list. each time, when I choose one scenario, there is a result in B2, for instance if I choose scenario1 in A1, b1=100; choose scenario2,b1=200; choose scenario3, b1=300. The question is, Can I make c1, c2 and c3 store b1 value based on different choosen scenario? For example, I choose scenario1 in drop-down list in A1, b1 show equal to 100, c1 store a result 100; if I change my choice in drop-down list to scenario2, b1 will be 200, but c1 will still keep 100 result, at the same time c2 will be 200. I tried Iteration function in Excel sheet, but it doesn't work, I THINK vba might be the only choice. could someone help me out? Thanks in advance. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
keep previous result when change, urgent
Thanks, Tom
I'm reading article, and trying to write but don't know how to complete it. Could anyone help me out? Thanks in advance. Private Sub Worksheet_Change(ByVal Target As Range) If a1 = 1 Then c1=b1 endif End Sub "Tom Ogilvy" wrote: Use the Change event in xl2000 and later See Chip Pearson's page on events: http://www.cpearson.com/excel/events.htm -- Regards, Tom Ogilvy "jiang" wrote in message ... I made a validation list in A1 for different scenarios, I could choose 3 different secnarios from this drop-down list. each time, when I choose one scenario, there is a result in B2, for instance if I choose scenario1 in A1, b1=100; choose scenario2,b1=200; choose scenario3, b1=300. The question is, Can I make c1, c2 and c3 store b1 value based on different choosen scenario? For example, I choose scenario1 in drop-down list in A1, b1 show equal to 100, c1 store a result 100; if I change my choice in drop-down list to scenario2, b1 will be 200, but c1 will still keep 100 result, at the same time c2 will be 200. I tried Iteration function in Excel sheet, but it doesn't work, I THINK vba might be the only choice. could someone help me out? Thanks in advance. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
keep previous result when change, urgent
Hi Jiang,
Try: '========================= Private Sub Worksheet_Change(ByVal Target As Range) Dim EndCell As Range If Not Intersect(Target, Range("A1")) Is Nothing Then If IsEmpty(Range("C1")) Then Set EndCell = Range("C1") ElseIf IsEmpty(Range("C2")) Then Set EndCell = Range("C2") Else Set EndCell = Cells(Rows.Count, "C").End(xlUp)(2) End If Select Case Target.Value Case 1 Range("B1").Value = 100 Case 2 Range("B1").Value = 200 Case 3 Range("B1").Value = 300 Case Else Range("B1").Value = "" End Select EndCell.Value = Range("B1").Value End If '<<========================= --- Regards, Norman "jiang" wrote in message ... Thanks, Tom I'm reading article, and trying to write but don't know how to complete it. Could anyone help me out? Thanks in advance. Private Sub Worksheet_Change(ByVal Target As Range) If a1 = 1 Then c1=b1 endif End Sub "Tom Ogilvy" wrote: Use the Change event in xl2000 and later See Chip Pearson's page on events: http://www.cpearson.com/excel/events.htm -- Regards, Tom Ogilvy "jiang" wrote in message ... I made a validation list in A1 for different scenarios, I could choose 3 different secnarios from this drop-down list. each time, when I choose one scenario, there is a result in B2, for instance if I choose scenario1 in A1, b1=100; choose scenario2,b1=200; choose scenario3, b1=300. The question is, Can I make c1, c2 and c3 store b1 value based on different choosen scenario? For example, I choose scenario1 in drop-down list in A1, b1 show equal to 100, c1 store a result 100; if I change my choice in drop-down list to scenario2, b1 will be 200, but c1 will still keep 100 result, at the same time c2 will be 200. I tried Iteration function in Excel sheet, but it doesn't work, I THINK vba might be the only choice. could someone help me out? Thanks in advance. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
keep previous result when change, urgent
Dear Norman,
d Thanks for your help, I tried your programs but the result is a bit different to my expectation. When I change the selection in drop-down list, the result won't replace existing in C1,C2and C3 content, more result shows following C column. such situation is because you used 'endcell' in program instead of specifying postion of result. But you example already helps me lots, I'm still trying to understand and adjust to my expectation. Thanks again and best wishes. jiang "Norman Jones" wrote: Hi Jiang, Try: '========================= Private Sub Worksheet_Change(ByVal Target As Range) Dim EndCell As Range If Not Intersect(Target, Range("A1")) Is Nothing Then If IsEmpty(Range("C1")) Then Set EndCell = Range("C1") ElseIf IsEmpty(Range("C2")) Then Set EndCell = Range("C2") Else Set EndCell = Cells(Rows.Count, "C").End(xlUp)(2) End If Select Case Target.Value Case 1 Range("B1").Value = 100 Case 2 Range("B1").Value = 200 Case 3 Range("B1").Value = 300 Case Else Range("B1").Value = "" End Select EndCell.Value = Range("B1").Value End If '<<========================= --- Regards, Norman "jiang" wrote in message ... Thanks, Tom I'm reading article, and trying to write but don't know how to complete it. Could anyone help me out? Thanks in advance. Private Sub Worksheet_Change(ByVal Target As Range) If a1 = 1 Then c1=b1 endif End Sub "Tom Ogilvy" wrote: Use the Change event in xl2000 and later See Chip Pearson's page on events: http://www.cpearson.com/excel/events.htm -- Regards, Tom Ogilvy "jiang" wrote in message ... I made a validation list in A1 for different scenarios, I could choose 3 different secnarios from this drop-down list. each time, when I choose one scenario, there is a result in B2, for instance if I choose scenario1 in A1, b1=100; choose scenario2,b1=200; choose scenario3, b1=300. The question is, Can I make c1, c2 and c3 store b1 value based on different choosen scenario? For example, I choose scenario1 in drop-down list in A1, b1 show equal to 100, c1 store a result 100; if I change my choice in drop-down list to scenario2, b1 will be 200, but c1 will still keep 100 result, at the same time c2 will be 200. I tried Iteration function in Excel sheet, but it doesn't work, I THINK vba might be the only choice. could someone help me out? Thanks in advance. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
keep previous result when change, urgent
Hi Jiang,
I am not sure that I correctly interpret your requirements but try: '=================== Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False Dim EndCell As Range Static i As Long If Not Intersect(Target, Range("A1")) Is Nothing Then If IsEmpty(Range("C1")) Then Set EndCell = Range("C1") ElseIf IsEmpty(Range("C2")) Then Set EndCell = Range("C2") ElseIf IsEmpty(Range("C3")) Then Set EndCell = Range("C3") Else Set EndCell = Range("C1").Offset(i) i = IIf(i + 1 2, 0, i + 1) End If Select Case Target.Value Case 1 Range("B1").Value = 100 Case 2 Range("B1").Value = 200 Case 3 Range("B1").Value = 300 Case Else Range("B1").Value = "" End Select EndCell.Value = Range("B1").Value End If Application.EnableEvents = True End Sub '=================== --- Regards, Norman "jiang" wrote in message ... Dear Norman, d Thanks for your help, I tried your programs but the result is a bit different to my expectation. When I change the selection in drop-down list, the result won't replace existing in C1,C2and C3 content, more result shows following C column. such situation is because you used 'endcell' in program instead of specifying postion of result. But you example already helps me lots, I'm still trying to understand and adjust to my expectation. Thanks again and best wishes. jiang "Norman Jones" wrote: Hi Jiang, Try: '========================= Private Sub Worksheet_Change(ByVal Target As Range) Dim EndCell As Range If Not Intersect(Target, Range("A1")) Is Nothing Then If IsEmpty(Range("C1")) Then Set EndCell = Range("C1") ElseIf IsEmpty(Range("C2")) Then Set EndCell = Range("C2") Else Set EndCell = Cells(Rows.Count, "C").End(xlUp)(2) End If Select Case Target.Value Case 1 Range("B1").Value = 100 Case 2 Range("B1").Value = 200 Case 3 Range("B1").Value = 300 Case Else Range("B1").Value = "" End Select EndCell.Value = Range("B1").Value End If '<<========================= --- Regards, Norman "jiang" wrote in message ... Thanks, Tom I'm reading article, and trying to write but don't know how to complete it. Could anyone help me out? Thanks in advance. Private Sub Worksheet_Change(ByVal Target As Range) If a1 = 1 Then c1=b1 endif End Sub "Tom Ogilvy" wrote: Use the Change event in xl2000 and later See Chip Pearson's page on events: http://www.cpearson.com/excel/events.htm -- Regards, Tom Ogilvy "jiang" wrote in message ... I made a validation list in A1 for different scenarios, I could choose 3 different secnarios from this drop-down list. each time, when I choose one scenario, there is a result in B2, for instance if I choose scenario1 in A1, b1=100; choose scenario2,b1=200; choose scenario3, b1=300. The question is, Can I make c1, c2 and c3 store b1 value based on different choosen scenario? For example, I choose scenario1 in drop-down list in A1, b1 show equal to 100, c1 store a result 100; if I change my choice in drop-down list to scenario2, b1 will be 200, but c1 will still keep 100 result, at the same time c2 will be 200. I tried Iteration function in Excel sheet, but it doesn't work, I THINK vba might be the only choice. could someone help me out? Thanks in advance. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How can I recover the previous data after save change | Excel Worksheet Functions | |||
| Copied dates automatically change to previous day | About this forum | |||
| Change the color of a cell change dependant on a result | Excel Programming | |||
| Previous value of cell before change | Excel Programming | |||
| HELP!! URGENT!! How to CHANGE TITLES IN CHARTS | Excel Programming | |||
Hybrid Mode
