| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Dynamic Range including some blank rows | Excel Discussion (Misc queries) | |||
| NUMBER OF ROWS IN A DYNAMIC RANGE | Excel Worksheet Functions | |||
| Number rows in a dynamic range | Excel Worksheet Functions | |||
| Dynamic Data Range | Excel Worksheet Functions | |||
| Can I use named range in data range box when creating pie chart? | Charts and Charting in Excel | |||
Linear Mode
