Don
Sorry I sent a reply to your e-mail address re this thread, I didn't realise
until I
returned to the ng.
$60 per hour whats that in Sterling, £34 will contact you later.
Regards
Dave

"Don Guillett" wrote:

Dave sent wb and I did this.

Dave,

I think this is what you want???
I have created a button and assigned to this macro which makes it easier to
refresh and updates the i column with each refresh.
I also fixed column W formulas. You may want to do something similar for the
adjacent columns.
You may also like the formula at o2 better than the one that I moved to o24

Sub RefreshData()
Range("a1").QueryTable.Refresh BackgroundQuery:=False
'or
'ActiveSheet.QueryTables(1).Refresh BackgroundQuery:=False
Range("L21").Insert (xlShiftDown)
Range("L21") = Application.Max(Range("j2:j20"))
Columns("a:n").AutoFit
End Sub

I'm sure tthere are many other improvements possible. I am for hire at $60
hourly as a consultant.

--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...
About to close down for the evening. Send, to MY address, a SMALL
workbook
example with what you need and I will take a look. Fully explain in the
workbook or email so I don't have to come back to the ng to get the info.

--
Don Guillett
SalesAid Software

"Dave" wrote in message
...
Don
Just had a another go at running the Code and this time it came up with
a
Run-time error '13': Type Mismatch and the Debugger highlighted the line
If Target < Range("$J$2:$J$20") Then
Any help to you, sorry i'm novice when it comes to programming.


"Don Guillett" wrote:

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