# keep previous result when change, urgent

1. ## 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?

2. ## Re: 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" <jiang@discussions.microsoft.com> wrote in message
news:A98BA1BF-75F0-45A8-B94E-D0E5F082FA6B@microsoft.com...
> 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?
>

3. ## Re: 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" <jiang@discussions.microsoft.com> wrote in message
> news:A98BA1BF-75F0-45A8-B94E-D0E5F082FA6B@microsoft.com...
> > 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?
> >

>
>
>

4. ## Re: 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" <jiang@discussions.microsoft.com> wrote in message
news:0DE65F3E-D04E-4E70-8953-989CA6B0E65B@microsoft.com...
> 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" <jiang@discussions.microsoft.com> wrote in message
>> news:A98BA1BF-75F0-45A8-B94E-D0E5F082FA6B@microsoft.com...
>> > 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?
>> >

>>
>>
>>

5. ## Re: 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" <jiang@discussions.microsoft.com> wrote in message
> news:0DE65F3E-D04E-4E70-8953-989CA6B0E65B@microsoft.com...
> > 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" <jiang@discussions.microsoft.com> wrote in message
> >> news:A98BA1BF-75F0-45A8-B94E-D0E5F082FA6B@microsoft.com...
> >> > 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. ## Re: 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" <jiang@discussions.microsoft.com> wrote in message
news:DAAC86AF-0145-4C02-A50E-F4A0A8939B71@microsoft.com...
> 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" <jiang@discussions.microsoft.com> wrote in message
>> news:0DE65F3E-D04E-4E70-8953-989CA6B0E65B@microsoft.com...
>> > 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" <jiang@discussions.microsoft.com> wrote in message
>> >> news:A98BA1BF-75F0-45A8-B94E-D0E5F082FA6B@microsoft.com...
>> >> > 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.
>> >>
>> >>
>> >>

>>
>>
>>

7. ## Re: keep previous result when change, urgent

Hi, Norman
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" <jiang@discussions.microsoft.com> wrote in message
> news:DAAC86AF-0145-4C02-A50E-F4A0A8939B71@microsoft.com...
> > 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" <jiang@discussions.microsoft.com> wrote in message
> >> news:0DE65F3E-D04E-4E70-8953-989CA6B0E65B@microsoft.com...
> >> > 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" <jiang@discussions.microsoft.com> wrote in message
> >> >> news:A98BA1BF-75F0-45A8-B94E-D0E5F082FA6B@microsoft.com...
> >> >> > 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.
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>

>
>
>

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1