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

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

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

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

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

If this post was helpful to you, please click YES below.



"yak141" wrote:

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

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

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((Ye arly!CX2:CX100<"")*ROW(Yearly!CX2:CX100))))),,,-10)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,400 Files:361 Subscribers:58
xdemechanik
---
"yak141" wrote:
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.

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

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

--
Regards
Roger Govier

"yak141" wrote in message
...
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