Is there a Function or Formula that can store data from say cell $J$21 and
store the
result in L21 in the same worksheet and every time the data in $J$21 changes
the result moves down 1 ie: L22 etc.
This would happen at the most 20 times a day.
Any help would be appreciated

right click sheet tabview codeinsert thissave

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$J$21" Then
Range("L21").Insert (xlShiftDown)
Range("L21") = Target * 2
End If
End Sub

--
Don Guillett
SalesAid Software

"Dave" wrote in message
...
Is there a Function or Formula that can store data from say cell $J$21 and
store the
result in L21 in the same worksheet and every time the data in $J$21
changes
the result moves down 1 ie: L22 etc.
This would happen at the most 20 times a day.
Any help would be appreciated

Hi Don
Thanks for your reply
I've done exactly as you suggested but its not working.
Other Modules in my Workbook are working correctly, I'm
using Excel 2003.
Any ideas?


"Don Guillett" wrote:

right click sheet tabview codeinsert thissave

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$J$21" Then
Range("L21").Insert (xlShiftDown)
Range("L21") = Target * 2
End If
End Sub

--
Don Guillett
SalesAid Software

"Dave" wrote in message
...
Is there a Function or Formula that can store data from say cell $J$21 and
store the
result in L21 in the same worksheet and every time the data in $J$21
changes
the result moves down 1 ie: L22 etc.
This would happen at the most 20 times a day.
Any help would be appreciated

Perhaps you placed in a REGULAR module instead of the sheet module as
instructed.
right click sheet tabview codeinsert thissave

--
Don Guillett
SalesAid Software

"Dave" wrote in message
...
Hi Don
Thanks for your reply
I've done exactly as you suggested but its not working.
Other Modules in my Workbook are working correctly, I'm
using Excel 2003.
Any ideas?


"Don Guillett" wrote:

right click sheet tabview codeinsert thissave

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$J$21" Then
Range("L21").Insert (xlShiftDown)
Range("L21") = Target * 2
End If
End Sub

--
Don Guillett
SalesAid Software

"Dave" wrote in message
...
Is there a Function or Formula that can store data from say cell $J$21
and
store the
result in L21 in the same worksheet and every time the data in $J$21
changes
the result moves down 1 ie: L22 etc.
This would happen at the most 20 times a day.
Any help would be appreciated

Hi Don
Thanks once again.
Sorry but done exactly as you instructed, right clicked sheet tab and inserted
function and saved.
Would it make any difference to the function if J21 was the result of MAX
Function
(J2:J20)
Dave

"Don Guillett" wrote:

Perhaps you placed in a REGULAR module instead of the sheet module as
instructed.
right click sheet tabview codeinsert thissave

--
Don Guillett
SalesAid Software

"Dave" wrote in message
...
Hi Don
Thanks for your reply
I've done exactly as you suggested but its not working.
Other Modules in my Workbook are working correctly, I'm
using Excel 2003.
Any ideas?


"Don Guillett" wrote:

right click sheet tabview codeinsert thissave

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$J$21" Then
Range("L21").Insert (xlShiftDown)
Range("L21") = Target * 2
End If
End Sub

--
Don Guillett
SalesAid Software

"Dave" wrote in message
...
Is there a Function or Formula that can store data from say cell $J$21
and
store the
result in L21 in the same worksheet and every time the data in $J$21
changes
the result moves down 1 ie: L22 etc.
This would happen at the most 20 times a day.
Any help would be appreciated

I guess there is a misunderstanding. If the target does not change by
entering something in it this macro will not fire. What cell(s) change, BY
ENTERING DATA, that then changes j21. If you are entering data in j2:j20
then the restriction would change.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target < Range("$j$1:$j$20") Then Exit Sub
Range("L21").Insert (xlShiftDown)
Range("L21") = Target * 2
End Sub

--
Don Guillett
SalesAid Software

"Dave" wrote in message
...
Hi Don
Thanks once again.
Sorry but done exactly as you instructed, right clicked sheet tab and
inserted
function and saved.
Would it make any difference to the function if J21 was the result of MAX
Function
(J2:J20)
Dave

"Don Guillett" wrote:

Perhaps you placed in a REGULAR module instead of the sheet module as
instructed.
right click sheet tabview codeinsert thissave

--
Don Guillett
SalesAid Software

"Dave" wrote in message
...
Hi Don
Thanks for your reply
I've done exactly as you suggested but its not working.
Other Modules in my Workbook are working correctly, I'm
using Excel 2003.
Any ideas?


"Don Guillett" wrote:

right click sheet tabview codeinsert thissave

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$J$21" Then
Range("L21").Insert (xlShiftDown)
Range("L21") = Target * 2
End If
End Sub

--
Don Guillett
SalesAid Software

"Dave" wrote in message
...
Is there a Function or Formula that can store data from say cell
$J$21
and
store the
result in L21 in the same worksheet and every time the data in
$J$21
changes
the result moves down 1 ie: L22 etc.
This would happen at the most 20 times a day.
Any help would be appreciated

Dave

You would need to capture a sheet event(the change of value in J21)

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo stoppit
Application.EnableEvents = False
If Target.Address = "$J$21" And Target.Value < "" Then
ActiveSheet.Cells(Rows.Count, 12).End(xlUp) _
.Offset(1, 0).Value = Target.Value
End If
stoppit:
Application.EnableEvents = True
End Sub

This code assumes L20 is not blank to start with.

Right-click on the sheet tab and "View Code".

Paste the above code into that module.

Enter something in L20 to start with. You can delete it after L21 is
populated first time the event fires.


Gord Dibben Excel MVP

On Tue, 1 Nov 2005 10:00:03 -0800, "Dave"
wrote:

Is there a Function or Formula that can store data from say cell $J$21 and
store the
result in L21 in the same worksheet and every time the data in $J$21 changes
the result moves down 1 ie: L22 etc.
This would happen at the most 20 times a day.
Any help would be appreciated

Hi Gord
Thanks for your reply
I'm afraid thats not working,i'm sorry but not very experienced at
programming.
Do you think it could be that J21 is the result of a MAX Function
ie:MAX(J2:J20)
is somehow stopping it from working.
Dave

"Gord Dibben" wrote:

Dave

You would need to capture a sheet event(the change of value in J21)

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo stoppit
Application.EnableEvents = False
If Target.Address = "$J$21" And Target.Value < "" Then
ActiveSheet.Cells(Rows.Count, 12).End(xlUp) _
.Offset(1, 0).Value = Target.Value
End If
stoppit:
Application.EnableEvents = True
End Sub

This code assumes L20 is not blank to start with.

Right-click on the sheet tab and "View Code".

Paste the above code into that module.

Enter something in L20 to start with. You can delete it after L21 is
populated first time the event fires.


Gord Dibben Excel MVP

On Tue, 1 Nov 2005 10:00:03 -0800, "Dave"
wrote:

Is there a Function or Formula that can store data from say cell $J$21 and
store the
result in L21 in the same worksheet and every time the data in $J$21 changes
the result moves down 1 ie: L22 etc.
This would happen at the most 20 times a day.
Any help would be appreciated