Creating a dynamic range always using the last 10 rows of data

[yak141]

I know how to setup a dynamic range. However, I need a set of formula that'll
allow the dynamic range to always use the last 10 rows on data in that range.
My current set of formula is
"=OFFSET(Yearly!$CX$2,0,0,COUNTA(Yearly!$CX:$CX),1)". It allows me to use the
entire range, but I only need the last 10 rows. Please help!Thanks

 

[Barb Reinhardt]

This assumes that you have more than 10 rows of data.

=OFFSET(Yearly!$CX$2,COUNTA(Yearly!$CX:$CX)-10,0,10,1).

 

[Max]

One idea is to dynamically point the OFFSET's anchor point at the bottommest
cell, and then simply use: -10 for the height param to grab the last 10 rows
up from there, something like this:
=OFFSET(INDIRECT("'Yearly'!CX"&SUMPRODUCT((MAX((Yearly!CX2:CX100<>"")*ROW(Yearly!CX2:CX100))))),,,-10)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,400 Files:361 Subscribers:58
xdemechanik

 

[Roger Govier]

Hi
One way
=INDEX(Yearly!$CX:$CX,COUNTA(Yearly!$CX:$CX)-10):INDEX(Yearly!$CX:$CX,COUNTA(Yearly!$CX:$CX))