Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Ranges - Help Please
I have a template with a sheet named Home. The template pulls data from
another sheet called work. The sheet home has a range set for the data area but the data from work does not always fill the range area and leaves rows blank. The data area starts at row a4 and the last column is M. The current range is a4:m182, a line of Totals is on row 183. Question: I need to be able to sort the data in 3 ways, alpha, numeric and date order. How do I make the range dynamic to enable me to make the sorts and ignore the blank cells on the template? (Blank cells are cells) Thanks in anticipation Topher |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Ranges - Help Please
Hi
Try =OFFSET($A$4,0,0,COUNTA($A$4:$A$182),13) -- Regards Roger Govier "Topher" wrote in message ... I have a template with a sheet named 'Home'. The template pulls data from another sheet called 'work'. The sheet home has a range set for the data area but the data from 'work' does not always fill the range area and leaves rows blank. The data area starts at row a4 and the last column is M. The current range is a4:m182, a line of 'Totals' is on row 183. Question: I need to be able to sort the data in 3 ways, alpha, numeric and date order. How do I make the range dynamic to enable me to make the sorts and ignore the blank cells on the template? (Blank cells are "" cells) Thanks in anticipation Topher |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Ranges - Help Please
Thankyou Roger but I had to substitute Count for Counta as it counted the
blank rows. Topher "Roger Govier" wrote: Hi Try =OFFSET($A$4,0,0,COUNTA($A$4:$A$182),13) -- Regards Roger Govier "Topher" wrote in message ... I have a template with a sheet named 'Home'. The template pulls data from another sheet called 'work'. The sheet home has a range set for the data area but the data from 'work' does not always fill the range area and leaves rows blank. The data area starts at row a4 and the last column is M. The current range is a4:m182, a line of 'Totals' is on row 183. Question: I need to be able to sort the data in 3 ways, alpha, numeric and date order. How do I make the range dynamic to enable me to make the sorts and ignore the blank cells on the template? (Blank cells are "" cells) Thanks in anticipation Topher |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Ranges - Help Please
Hi
Then I assume that your "blank" rows were really formulae that were returning a Null value. Anyway, glad you got it worked out and thanks for responding. -- Regards Roger Govier "Topher" wrote in message ... Thankyou Roger but I had to substitute Count for Counta as it counted the blank rows. Topher "Roger Govier" wrote: Hi Try =OFFSET($A$4,0,0,COUNTA($A$4:$A$182),13) -- Regards Roger Govier "Topher" wrote in message ... I have a template with a sheet named 'Home'. The template pulls data from another sheet called 'work'. The sheet home has a range set for the data area but the data from 'work' does not always fill the range area and leaves rows blank. The data area starts at row a4 and the last column is M. The current range is a4:m182, a line of 'Totals' is on row 183. Question: I need to be able to sort the data in 3 ways, alpha, numeric and date order. How do I make the range dynamic to enable me to make the sorts and ignore the blank cells on the template? (Blank cells are "" cells) Thanks in anticipation Topher |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Ranges | Excel Discussion (Misc queries) | |||
Dynamic Ranges with ADO | Excel Discussion (Misc queries) | |||
Dynamic Ranges | Excel Discussion (Misc queries) | |||
dynamic ranges | Excel Programming | |||
Dynamic Formulas with Dynamic Ranges | Excel Worksheet Functions |