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?

 

[VBA Geek]

does this do what you want?

=INDEX($AC$51:$AC$76,MATCH(99^99,$AC$51:$AC$76)-5+ROWS(AE$51:AE51))

 

[dannyi21i]

it does the same thing as my last code as if all the cells have the same letter it turns all 5 cells that have this code to #N/A

 

[Caleeco]

Can you post a screenshot of your data? You could possibly do this with the INDIRECT & ROW functions.

Cheers,
Caleeco

 

[dannyi21i]

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

g4 to g8 is were it should show the last 5

<a href="http://tinypic.com?ref=rlkmkh" target="_blank"><img src="http://i64.tinypic.com/rlkmkh.png" border="0" alt="Image and video hosting by TinyPic"></a>

 

[Caleeco]

Hello,

Thanks for the picture. I would do something like this:

Put this in cell G4 and copy down:

=INDIRECT("C" & COUNTA(C:C)+2-(ROW()-4))

Cheers
Caleeco

 

[dannyi21i]

Thankyou! it works well, how would i edit this to read from a list in other cell line, for example 'AC' cell line?

 

[Caleeco]

Thankyou! it works well, how would i edit this to read from a list in other cell line, for example 'AC' cell line?

You would just change the 'C's to all "AC's.

=INDIRECT("AC" & COUNTA(AC:AC)+2-(ROW()-4))

However, if you see in the equation. there are two other numbers in the equation! 2 & 4.

The first number is the number of blank rows you have above your data in column AC. the second number is the row number where your first entry in column AE needs to go. Let me know if you need help setting this up!

Caleeco

 

[dannyi21i]

i changed them however its just displaying '0's

<a href="http://tinypic.com?ref=a42343" target="_blank"><img src="http://i65.tinypic.com/a42343.png" border="0" alt="Image and video hosting by TinyPic"></a>

 

[Caleeco]

So for your screenshot, the formula in AD52 would be

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

Let me know if that works
Cheers

Caleeco