Charting the last 20 numbers in a column containing several hundred

[Peter_Collins]

I want to select and copy the last say 20 numbers in a column of data, which can have more numbers added to it, onto another sheet with a line graph in it. So I want the last/most recent numbers at the bottom of the column. I think that I need to use the INDEX function but I'm not clear about the correct syntax. I'm using OFFICE365, on a laptop but friends may be using older versions.

[#2 · Accepted solution]

@Peter_Collins 

Let's say the data are on Sheet1 in A2:An, with a header in Sheet1!A1.

On the other sheet, enter the same header in A1.

In A2, enter the formula

=INDEX(Sheet1!A:A,COUNTA(Sheet1!A:A)-21+ROW())

Fill down to A21.

[#3]

Hans, many thanks for replying so quickly - very kind of you.
I've tried using the formula that you've suggested, but I must be doing something wrong. Is Sheet1 the name of the workbook or the name of the worksheet in the workbook.
My workbook is called "QC Report - Update 20102021" :( and my worksheet is called "All data" the data is contained in column U, which has a label at the top of Density.
So I tried entering =INDEX(All data!U:U,COUNTA(All data!U:U)-21+ROW())
and =INDEX(QC Report - Update 20102021!U:U,COUNTA(QC Report - Update 20102021!U:U)-21+ROW())
Both gave me an error of #Name?
Thanks again for any help that you can give me. Peter

[#4]

@Peter_Collins 

Use

=INDEX('[QC Report - Update 20102021.xlsx]All data'!U:U,COUNTA('[QC Report - Update 20102021.xlsx]All data'!U:U)-21+ROW())

[#5]

Brilliant, many thanks Hans I'll try this in in an hour or so. Best wishes, Peter