| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Function or Formula
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 |
|
#2
|
|||
|
|||
|
Function or Formula
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 |
|
#3
|
|||
|
|||
|
Function or Formula
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 |
|
#4
|
|||
|
|||
|
Function or Formula
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 |
|
#6
|
|||
|
|||
|
Function or Formula
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 |
|
#7
|
|||
|
|||
|
Function or Formula
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 |
|
#8
|
|||
|
|||
|
Function or Formula
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 |
|
#9
|
|||
|
|||
|
Function or Formula
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 "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 |
|
#10
|
|||
|
|||
|
Function or Formula
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 |
|
#11
|
|||
|
|||
|
Function or Formula
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 |
|
#12
|
|||
|
|||
|
Function or Formula
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 |
|
#13
|
|||
|
|||
|
Function or Formula
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| formula within IF function | Excel Discussion (Misc queries) | |||
| use a formula as a cell reference in a function | Excel Worksheet Functions | |||
| Is there a formula, function or macro that accounts for shading of a cell or row? | Excel Discussion (Misc queries) | |||
| clock | Excel Worksheet Functions | |||
| function of "+" in the formula "=+(B19/50)*B10 | Excel Discussion (Misc queries) | |||
Linear Mode
