Excel 2000 List

[vestax22]

Hi, I'm creating a train report for a company using Excel.

This report initialises once a month and keeps an average of the last thirty trains dumped.

What I want to do is have a list on a separate worksheet that keeps track of the last thirty trains dumped. This list will always be equal to thirty so when a new train adds on to the list the oldest train in the list dissapears. Meanwhile there is a formula that calculates the average of the thirty last trains. Does anybody have any idea how to go about doing this?


Thx

 

[winstonep]

With Excel its (and most of Office) its often easier to record a macro with the key strokes for everything that you want to achieve and then refine it in the VBA editor.

So, I would have two worksheets. One containing the train list and one containing dumped trains. So, when you select a train on sheet1 (train list), you would next want to count the number of dumped trains listed. This is where macro recording fails, so if you have an existing list just select them. Make sure you do this by selecting the first one and then pressing shift and end, followed by the appropriate cursor key e.g. down.
Now when you edit the macro, insert a test for
=countblank(a2:a32)

The range here is only an example but that would be where your list of 30 trains would be. If the result is = 0, go to the top of the list using

Application.Goto Reference:="R2C1"

(using my range as an example) and execute a delete by selecting the cell using

Range("A2&quot.Select

and then delete using

Selection.Delete Shift:=xlUp

Now all you have to do is go to the end of that list by using

Range("A2&quot.Select
Selection.End(xlDown).Select

and copy the train information from the selected cell in sheet1. Remember for anything that you can't work out how to do, if you can do it by hand then record it and look at the results.

Paul Winstone

http://www.logicacmg.com

paul.winstone@logicacmg.com

 

[vestax22]

Thx for the reply


I think I got the solution though. The thing is that I have a button that calls a userform for data input (trains). So every time the user inputs a train I modify the list by copying the whole list except for the last train of the list one line down and inserting the latest train on top of the list. There is a formula on the side that calculates the average which I copy to the other worksheet in the appropriate place.

 

[KenWright]

Not sure if it will help, but to keep the average of the last 30 trains, given that you want the range to be the same each time, you can use the following formula, which will not change as a result of you adding/deleting rows or columns:-

=AVERAGE(INDIRECT("A1:A30&quot)

Regards
Ken.................