Copy last 10 values to a table on another worksheet

[djdave007]

Hi,
I am wondering if anyone can help with a copy and paste issue.
I have a raw data list on sheet A, with 100+ values in Column A.

I would like if possible, for the last 10 values be copied to Sheet B and be entered into row cells A2, B2, C2...J2

Everytime the another value is entered in Sheet A, in Column A i.e. 101st value, the values in Sheet B - A2........J2 change to show the NEW last 10 values.

Is this possible?

thanks for any help
dave

 

[Domenic]

Assuming that Column A, starting in Row 2, in Sheet1, contains the source data, try the following...

A2, copied across:

To return first to last...

=INDEX(Sheet1!$A$2:$A$65536,MATCH(9.99999999999999E+307,Sheet1!$A$2:$A$65536)+COLUMNS($C2:C2)-10)

To return last to first...

=INDEX(Sheet1!$A$2:$A$65536,MATCH(9.99999999999999E+307,Sheet1!$A$2:$A$65536)-COLUMNS($A2:A2)+1)

Note that if the data contains text values, instead of numerical values, replace...

9.99999999999999E+307

with

REPT("z",255)

Hope this helps!

 

[djdave007]

works perfectly...lovely jubbly...

thank you v much

dave

 

[Domenic]

You're very welcome! By the way, since the formula is first being entered in A2 and then copied across, this part of the first formula...

COLUMNS($C2:C2)

...should have been...

COLUMNS($A2:A2)

Note, however, both will return the same result.