|
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. |
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. |
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. |
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. |
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. |
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. |
keep previous result when change, urgent
Hi, Norman
Thanks for your reply and your answer helps me a lot. I couldn't post message untill today. Actually both of way you made are all very useful in different cases and really gave me a nice structure to follow (I'm still a basic user of Excel with little knowledge about VBA, by reading help from VBA, I could more or less understand why such codes are written ). is it possible to show all results by changing selection in drop-down list, for instance, cell C1-C3 keep all three results but in specific FIXED positions, but I need result 1 is always shown in 'C1', result 2 is always shown in 'C2', result 3 is always shown in 'C3'. if I change number of Scenario in code, the new result will be shown in fixed position. A B C 1 drop-down list showing selection in list result1(100) 2 result2(200) 3 result3(300) The key is in the following code you wrote, 1) Set EndCell = Cells(Rows.Count, "C").End(xlUp)(2) in your first code, when I select different scenario in A1's drop-down list, the results will be shown by following C column, can't replace existing result; 2) Set EndCell = Range("C1").Offset(i) i = IIf(i + 1 2, 0, i + 1) In your second code, when I select drop-down list, the new result will repeatly shown in C1,C2and C3, but not in their previous/fixed postion. Now is it possible to fix location of result ? I hope I explain clear this time. Your answer helps me to compare different results, which all come from same calculation, at the same graph. Thanks again Best regards jiang "Norman Jones" wrote: 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. |
| All times are GMT +1. The time now is 05:28 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com