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?

 

[dannyi21i]

still coming up with '0'

i thought it woud have the code below but it comes up with '0' on that aswell.

=INDIRECT("AC" & COUNTA(AC:AC)+29-(ROW()-52))

 

[VBA Geek]

http://tinypic.com/r/rlkmkh/9

g4 to g8 is were it should show the last 5

G4: =INDEX($C$3:$C$11,MATCH(REPT("Z",255),$C$3:$C$11)-ROWS(G$4:G4)+1)

Excel 2013/2016
	A	B	C	D	E	F	G	H
1
2
3			letter
4			t				e
5			b				r
6			a				t
7			n				n
8			n				n
9			t
10			r
11			e
Sheet1

 

[dannyi21i]

G4: =INDEX($C$3:$C$11,MATCH(REPT("Z",255),$C$3:$C$11)-ROWS(G$4:G4)+1)

Excel 2013/2016

	A	B	C	D	E	F	G	H
t	e
b	r
a	t
n	n
n	n
t
r
e

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]letter[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

that just comes back with true and false

 

[MARK858]

that just comes back with true and false

It doesn't for me, it returns...

Excel Workbook
	A	B	C	D	E	F	G
3			letter
4			t				e
5			b				r
6			a				t
7			n				n
8			n				n
9			t				a
10			r				b
11			e				t
Sheet1

 

[VBA Geek]

that just comes back with true and false

you dont have to input the formula G4=INDEX($C$3:$C$11,MATCH(REPT("Z",255),$C$3:$C$11)-ROWS(G$4:G4)+1)

I meant that in the cell G4, you input the formula=INDEX($C$3:$C$11,MATCH(REPT("Z",255),$C$3:$C$11)-ROWS(G$4:G4)+1)

 

[dannyi21i]

It doesn't for me, it returns...

Sheet1

	A	B	C	D	E	F	G
		letter
		t				e
		b				r
		a				t
		n				n
		n				n
		t				a
		r				b
		e				t

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula G4 =INDEX($C$3:$C$11,MATCH(REPT("Z",255),$C$3:$C$11)-ROWS(G$4:G4)+1) G5 =INDEX($C$3:$C$11,MATCH(REPT("Z",255),$C$3:$C$11)-ROWS(G$4:G5)+1) G6 =INDEX($C$3:$C$11,MATCH(REPT("Z",255),$C$3:$C$11)-ROWS(G$4:G6)+1) G7 =INDEX($C$3:$C$11,MATCH(REPT("Z",255),$C$3:$C$11)-ROWS(G$4:G7)+1) G8 =INDEX($C$3:$C$11,MATCH(REPT("Z",255),$C$3:$C$11)-ROWS(G$4:G8)+1) G9 =INDEX($C$3:$C$11,MATCH(REPT("Z",255),$C$3:$C$11)-ROWS(G$4:G9)+1) G10 =INDEX($C$3:$C$11,MATCH(REPT("Z",255),$C$3:$C$11)-ROWS(G$4:G10)+1) G11 =INDEX($C$3:$C$11,MATCH(REPT("Z",255),$C$3:$C$11)-ROWS(G$4:G11)+1) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

i have adapted this to the way i want it and it works perfectly so far, thankyou so much!

 

[MARK858]

i have adapted this to the way i want it and it works perfectly so far, thankyou so much!

Not me you need to thank it is the formula VBA Geek posted.

 

[B___P]

Hi dannyi21i,
give this formula a try
=INDEX($C$4:$C$11,LARGE(ROW($C$4:$C$11)-ROW($C$3),ROW(A1)))
It must be entered with CTRL SHIFT ENTER and then drag it down.

hth

 

[dannyi21i]

G4: =INDEX($C$3:$C$11,MATCH(REPT("Z",255),$C$3:$C$11)-ROWS(G$4:G4)+1)

Excel 2013/2016

	A	B	C	D	E	F	G	H
t	e
b	r
a	t
n	n
n	n
t
r
e

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]letter[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

thanks mate this code helped me out so much been trying to figure the code i need for weeks.

 

[dannyi21i]

Hi dannyi21i,
give this formula a try
=INDEX($C$4:$C$11,LARGE(ROW($C$4:$C$11)-ROW($C$3),ROW(A1)))
It must be entered with CTRL SHIFT ENTER and then drag it down.

hth

this code just came back with '0's mate but i found the code i need from someone else who commented above, thankyou for your efforts though.