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

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

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

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