Dear All,

I am writing a spreadsheet for my wife who is a teacher in order for her to be able to track the results of the children through school. There are 4 year groups with 3 classes in each year group. The test is taken once each term so each child takes the test 3 times in a year.

I have a sheet which tabulates all the data by year and that is fine but what I want to do now is create a worksheet which tracks the results the test each term throughout that childs time in the school (ie through all 4 years). I want this to run automated to avoid mistakes.

The problem:
I have in Column A a long collated list of all the children in the school (in alpahabetical order not year group order). In a hidden row A i have a set of vlookup functions which will look up the childs name and put the appropriate result in the right box. This is fine but what I want it some code so that at the end of the year, it will copy all the vlookup functions and past the data back in as values (to preserve the results the children got in say Year 1 before they moved onto year 2). This would not be the case if I left the functions in there as then the children would be registered as year 2 and so the Year 1 data would go null again.

So I want to copy the vlookup equations in thehidden row, drag them down to cover the number of children, copy this data and paste it back in as values, then (the hard bit for me) I want to put the vllokup functions back in but only into cells which have no value (it may have no value or be a 0 or be an error message all of which are fine) in already (thus preserving the previous years results).

Ca anyone help me with the code I would need to achieve this?

Many thanks in advance.

Russ