How do i get the last 5 cells out of a column

[dannyi21i]

in my work sheet i want to get the last 5 cells of a column and display them in 5 other cells.

i used the formula below and dragged it down 5 to show the last 5 cells and it worked to an extent but if the last 5 cells are all the same it messes up and doesnt show the last 5 cells;

=INDEX(AC51:AC76,MATCH(99^99,AC51:AC76,1)-1)

please can anyone help me?

 

[Ingolf]

Cross post
https://www.excelforum.com/excel-general/1221591-want-to-show-the-last-5-cells-of-a-column.html

 

[Fluff]

Cross-Posting
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[B___P]

Hi Fluff,
I am not sure it is allowed to post an answer in a thresd with a crossposting warning but for our friends I try this short formula simple to underrstand and easy to customize:
=INDEX($C$4:$C$11,ROWS($C$4:$C$11)-ROW(A1)+1)

ciao

 

[Fluff]

Hi Fluff,
I am not sure it is allowed to post an answer in a thresd with a crossposting warning

You're more than welcome to continue posting.
We just like the OP to let people know.

 

[dannyi21i]

Cross-Posting
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

yh the other site has the same rules and someone called me out on it there as well, i should have read the rules, my apologies for breaking the rules.

heres the link to my other post. thankyou all for your help as well.


https://www.excelforum.com/excel-general/1221591-want-to-show-the-last-5-cells-of-a-column-2.html

 

[dannyi21i]

ran into a problem with this code, if the cells im trying to get the last 5 from are coded but showing black it just shows blank for the last 5 instead of showing cells that have numbers in, is there anyway around this?

D4 and D3 should be showing the last 5 as well but its showing blank as the coding isnt showing anything.

 

[dannyi21i]

bump

 

[VBA Geek]

<b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D3</th><td style="text-align:left">{=IF(<font color="Blue">ROWS(<font color="Red">D$3:D3</font>)>5,"",INDEX(<font color="Red">$C$3:$C$100,LARGE(<font color="Green">IF(<font color="Purple">$C$3:$C$100<>"",ROW(<font color="Teal">$C$3:$C$100</font>)-ROW(<font color="Teal">$C$3</font>)+1</font>),ROWS(<font color="Purple">D$3:D3</font>)</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.</td></tr></table><br />

Excel 2013/2016
	A	B	C	D	E
1
2			W/L/D	LAST 5
3			D	J
4			D	D
5			W	L
6			P	L
7			L	P
8			L
9			D
10			J
11
12
13
Sheet1

 

[dannyi21i]

ty mate this worked perfectly