SOLVED

Charting the last 20 numbers in a column containing several hundred

%3CLINGO-SUB%20id%3D%22lingo-sub-2871790%22%20slang%3D%22en-US%22%3ECharting%20the%20last%2020%20numbers%20in%20a%20column%20containing%20several%20hundred%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2871790%22%20slang%3D%22en-US%22%3E%3CP%3EI%20want%20to%20select%20and%20copy%20the%20last%20say%2020%20numbers%20in%20a%20column%20of%20data%2C%20which%20can%20have%20more%20numbers%20added%20to%20it%2C%20onto%20another%20sheet%20with%20a%20line%20graph%20in%20it.%20So%20I%20want%20the%20last%2Fmost%20recent%20numbers%20at%20the%20bottom%20of%20the%20column.%20I%20think%20that%20I%20need%20to%20use%20the%20INDEX%20function%20but%20I'm%20not%20clear%20about%20the%20correct%20syntax.%20I'm%20using%20OFFICE365%2C%20on%20a%20laptop%20but%20friends%20may%20be%20using%20older%20versions.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2871790%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2871847%22%20slang%3D%22en-US%22%3ERe%3A%20Charting%20the%20last%2020%20numbers%20in%20a%20column%20containing%20several%20hundred%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2871847%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1192493%22%20target%3D%22_blank%22%3E%40Peter_Collins%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20say%20the%20data%20are%20on%20Sheet1%20in%20A2%3AAn%2C%20with%20a%20header%20in%20Sheet1!A1.%3C%2FP%3E%0A%3CP%3EOn%20the%20other%20sheet%2C%20enter%20the%20same%20header%20in%20A1.%3C%2FP%3E%0A%3CP%3EIn%20A2%2C%20enter%20the%20formula%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DINDEX(Sheet1!A%3AA%2CCOUNTA(Sheet1!A%3AA)-21%2BROW())%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFill%20down%20to%20A21.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2871923%22%20slang%3D%22en-US%22%3ERe%3A%20Charting%20the%20last%2020%20numbers%20in%20a%20column%20containing%20several%20hundred%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2871923%22%20slang%3D%22en-US%22%3EHans%2C%20many%20thanks%20for%20replying%20so%20quickly%20-%20very%20kind%20of%20you.%3CBR%20%2F%3EI've%20tried%20using%20the%20formula%20that%20you've%20suggested%2C%20but%20I%20must%20be%20doing%20something%20wrong.%20Is%20Sheet1%20the%20name%20of%20the%20workbook%20or%20the%20name%20of%20the%20worksheet%20in%20the%20workbook.%3CBR%20%2F%3EMy%20workbook%20is%20called%20%22QC%20Report%20-%20Update%2020102021%22%20%3CLI-EMOJI%20id%3D%22lia_disappointed-face%22%20title%3D%22%3Adisappointed_face%3A%22%3E%3C%2FLI-EMOJI%3E%20and%20my%20worksheet%20is%20called%20%22All%20data%22%20the%20data%20is%20contained%20in%20column%20U%2C%20which%20has%20a%20label%20at%20the%20top%20of%20Density.%3CBR%20%2F%3ESo%20I%20tried%20entering%20%3DINDEX(All%20data!U%3AU%2CCOUNTA(All%20data!U%3AU)-21%2BROW())%3CBR%20%2F%3Eand%20%3DINDEX(QC%20Report%20-%20Update%2020102021!U%3AU%2CCOUNTA(QC%20Report%20-%20Update%2020102021!U%3AU)-21%2BROW())%3CBR%20%2F%3EBoth%20gave%20me%20an%20error%20of%20%23Name%3F%3CBR%20%2F%3EThanks%20again%20for%20any%20help%20that%20you%20can%20give%20me.%20Peter%3C%2FLINGO-BODY%3E
New Contributor

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.

4 Replies
best response confirmed by Peter_Collins (New Contributor)
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.

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

@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())

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