Function or Formula

D

Dave

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
 
D

Don Guillett

right click sheet tab>view code>insert this>save

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
 
D

Dave

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?
 
G

Gord Dibben

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
 
D

Don Guillett

Perhaps you placed in a REGULAR module instead of the sheet module as
instructed.
 
D

Dave

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
 
D

Dave

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
 
D

Don Guillett

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
 
D

Dave

Hi Don
Still not working sorry.
Not all of the cells in J2:J20 have data in, that should not make any
difference
to the MAX Function in Cell J21 though.
I download the data every day all the other functions work OK in this
worksheet
I will have to carry on entering the data manually in L21 etc. unless you can
think of something else.
Thanks again
Dave
 
D

Dave

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.
 
D

Don Guillett

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.
 
D

Don Guillett

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.
 
D

Dave

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
 
Top