Inserting Offset equation using VBA

Hi Folks,

I'm having trouble and wondered if anyone could help:

I have two worksheets. The first sheet contains a column of 10 values. I have named the cell in the last row "final"

I want to put these values in to the second sheet using the offset function. So the first cell in the second sheet would have the equation

=OFFSET(final,-9,0)

The second cell would have the equation

=OFFSET(final, -8, 0)

The third would be

=OFFSET(final, -7, 0)

and so on down to the last row which would be

=OFFSET(final, 0, 0)

and sheet one and sheet two would be identical. How would I accomplish this? I don't want to use the offset vba function because I want the formula inside the cells. The trouble I'm having is with the row offset number changing, I don't know how to code that.

Any ideas would be appreciated, many thanks for your help,

AR