| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
creating outcome series from formula
I am working with a formula in which the value of one of the inputcells
changes dynamically (stock quote). What can i do to automatically put the changing outcome of the formula in a series in a colum? Can anybody help me out ? Maurice Amsterdam, The Netherlands |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
creating outcome series from formula
Maurice,
The only way I can think of is to use a macro. If that's okay, consider the following. The formula is in B2. The list of outcomes will be in C. The input cell is A2. You need to enter a macro into the sheet that this data is on. The following macro should do it: Private Sub Worksheet_Change(ByVal Target As Range) If Target = Range("A2") Then Range("c65536").End(xlUp).Offset(1) = Range("B2") End If End Sub "Maurice" wrote: I am working with a formula in which the value of one of the inputcells changes dynamically (stock quote). What can i do to automatically put the changing outcome of the formula in a series in a colum? Can anybody help me out ? Maurice Amsterdam, The Netherlands |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
creating outcome series from formula
that's great Art, thank you very much! Allow me one follow up question; if i
want to limit my list let's say to 100 outcomes, and when outcome 101 appears, outcome 1 is deleted and replaced by outcome 2 and so forth, hence the list of 100 would change dynamically, how would i write that in a macro? again, thanks very much! Maurice "Art" wrote: Maurice, The only way I can think of is to use a macro. If that's okay, consider the following. The formula is in B2. The list of outcomes will be in C. The input cell is A2. You need to enter a macro into the sheet that this data is on. The following macro should do it: Private Sub Worksheet_Change(ByVal Target As Range) If Target = Range("A2") Then Range("c65536").End(xlUp).Offset(1) = Range("B2") End If End Sub "Maurice" wrote: I am working with a formula in which the value of one of the inputcells changes dynamically (stock quote). What can i do to automatically put the changing outcome of the formula in a series in a colum? Can anybody help me out ? Maurice Amsterdam, The Netherlands |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
creating outcome series from formula
Maurice,
I made a few changes to the routine including the one to limit it to 100 outcomes: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address < "$A$2" Then Exit Sub Range("C65536").End(xlUp).Offset(1) = Range("B2") If Range("C65536").End(xlUp).Address = "$C$102" _ Then Range("$C$2").Delete Shift:=xlUp End Sub The previous routine crashed if you cut and pasted more than 1 cell anywhere in the worksheet. This should work better. "Maurice" wrote: that's great Art, thank you very much! Allow me one follow up question; if i want to limit my list let's say to 100 outcomes, and when outcome 101 appears, outcome 1 is deleted and replaced by outcome 2 and so forth, hence the list of 100 would change dynamically, how would i write that in a macro? again, thanks very much! Maurice "Art" wrote: Maurice, The only way I can think of is to use a macro. If that's okay, consider the following. The formula is in B2. The list of outcomes will be in C. The input cell is A2. You need to enter a macro into the sheet that this data is on. The following macro should do it: Private Sub Worksheet_Change(ByVal Target As Range) If Target = Range("A2") Then Range("c65536").End(xlUp).Offset(1) = Range("B2") End If End Sub "Maurice" wrote: I am working with a formula in which the value of one of the inputcells changes dynamically (stock quote). What can i do to automatically put the changing outcome of the formula in a series in a colum? Can anybody help me out ? Maurice Amsterdam, The Netherlands |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
creating outcome series from formula
thanks a million Art, i made a few small alterations and now it works
perfect, it looks like this: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" Then Range("C65536").End(xlUp).Offset(1, 0) = Range("B1") + Target If Range("C65536").End(xlUp).Address = "$C$10" _ Then Range("$C$2").Delete Shift:=xlUp End If End Sub Thanks a lot and greetings from a sunny Amsterdam.. "Art" wrote: Maurice, I made a few changes to the routine including the one to limit it to 100 outcomes: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address < "$A$2" Then Exit Sub Range("C65536").End(xlUp).Offset(1) = Range("B2") If Range("C65536").End(xlUp).Address = "$C$102" _ Then Range("$C$2").Delete Shift:=xlUp End Sub The previous routine crashed if you cut and pasted more than 1 cell anywhere in the worksheet. This should work better. "Maurice" wrote: that's great Art, thank you very much! Allow me one follow up question; if i want to limit my list let's say to 100 outcomes, and when outcome 101 appears, outcome 1 is deleted and replaced by outcome 2 and so forth, hence the list of 100 would change dynamically, how would i write that in a macro? again, thanks very much! Maurice "Art" wrote: Maurice, The only way I can think of is to use a macro. If that's okay, consider the following. The formula is in B2. The list of outcomes will be in C. The input cell is A2. You need to enter a macro into the sheet that this data is on. The following macro should do it: Private Sub Worksheet_Change(ByVal Target As Range) If Target = Range("A2") Then Range("c65536").End(xlUp).Offset(1) = Range("B2") End If End Sub "Maurice" wrote: I am working with a formula in which the value of one of the inputcells changes dynamically (stock quote). What can i do to automatically put the changing outcome of the formula in a series in a colum? Can anybody help me out ? Maurice Amsterdam, The Netherlands |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
creating outcome series from formula
Art - i am so close but yet not there! I am running the code but the thing is
that excel doesn't 'read' the changing targetcel unless i put the cursor into the cell and push enter after each digit. Any way to solve this? Maurice "Maurice" wrote: thanks a million Art, i made a few small alterations and now it works perfect, it looks like this: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" Then Range("C65536").End(xlUp).Offset(1, 0) = Range("B1") + Target If Range("C65536").End(xlUp).Address = "$C$10" _ Then Range("$C$2").Delete Shift:=xlUp End If End Sub Thanks a lot and greetings from a sunny Amsterdam.. "Art" wrote: Maurice, I made a few changes to the routine including the one to limit it to 100 outcomes: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address < "$A$2" Then Exit Sub Range("C65536").End(xlUp).Offset(1) = Range("B2") If Range("C65536").End(xlUp).Address = "$C$102" _ Then Range("$C$2").Delete Shift:=xlUp End Sub The previous routine crashed if you cut and pasted more than 1 cell anywhere in the worksheet. This should work better. "Maurice" wrote: that's great Art, thank you very much! Allow me one follow up question; if i want to limit my list let's say to 100 outcomes, and when outcome 101 appears, outcome 1 is deleted and replaced by outcome 2 and so forth, hence the list of 100 would change dynamically, how would i write that in a macro? again, thanks very much! Maurice "Art" wrote: Maurice, The only way I can think of is to use a macro. If that's okay, consider the following. The formula is in B2. The list of outcomes will be in C. The input cell is A2. You need to enter a macro into the sheet that this data is on. The following macro should do it: Private Sub Worksheet_Change(ByVal Target As Range) If Target = Range("A2") Then Range("c65536").End(xlUp).Offset(1) = Range("B2") End If End Sub "Maurice" wrote: I am working with a formula in which the value of one of the inputcells changes dynamically (stock quote). What can i do to automatically put the changing outcome of the formula in a series in a colum? Can anybody help me out ? Maurice Amsterdam, The Netherlands |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| wrong calculation in excel? formula outcome < cell value?? | Excel Worksheet Functions | |||
| format the outcome of a formula | Excel Discussion (Misc queries) | |||
| Creating new series requires div cell val by itself . How to do t. | Excel Discussion (Misc queries) | |||
| Stop % showing in outcome of formula | Excel Worksheet Functions | |||
| Creating a series of data | Excel Worksheet Functions | |||
Hybrid Mode
