Forum Discussion
Charting the last 20 numbers in a column containing several hundred
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.
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.
4 Replies
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.
- 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. PeterUse
=INDEX('[QC Report - Update 20102021.xlsx]All data'!U:U,COUNTA('[QC Report - Update 20102021.xlsx]All data'!U:U)-21+ROW())
