named range / offset

I am trying to create a dynamic range.

so far i have had luck with the suggestion form another user to use:

=OFFSET($C$8,0,0,COUNTA($C:$C),1)

that works when the range is in one column.

what do i change if i want to expand the range to say, AF?

I have tried

=OFFSET($C$8:$AF$8,0,0,COUNTA($C:$C),1)
and
=OFFSET($C$8,0,0,COUNTA($C:$AF),1)

but neither seemed to work. what am i missing?

thanks!

and as usual as soon as i post i look at the help for Offset.....

=OFFSET($C$8,0,0,COUNTA($C:$C),COUNTA($C:$AF))
gives you both height and width



"Gixxer_J_97" wrote:

> I am trying to create a dynamic range.
>
> so far i have had luck with the suggestion form another user to use:
>
> =OFFSET($C$8,0,0,COUNTA($C:$C),1)
>
> that works when the range is in one column.
>
> what do i change if i want to expand the range to say, AF?
>
> I have tried
>
> =OFFSET($C$8:$AF$8,0,0,COUNTA($C:$C),1)
> and
> =OFFSET($C$8,0,0,COUNTA($C:$AF),1)
>
> but neither seemed to work. what am i missing?
>
> thanks!

always a good idea to check help. (first?
however you've got it almost right..

the width will get TOO big and the formula will be too slow.

you'll need as wide as the number of headers, correct?
assuming those are in row 8..

=OFFSET($C$8,0,0,COUNTA($C:$C),COUNTA($8:$8))




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Gixxer_J_97 wrote :

> and as usual as soon as i post i look at the help for Offset.....
>
> =OFFSET($C$8,0,0,COUNTA($C:$C),COUNTA($C:$AF))
> gives you both height and width
>
>
>
> "Gixxer_J_97" wrote:
>
> > I am trying to create a dynamic range.
> >
> > so far i have had luck with the suggestion form another user to use:
> >
> > =OFFSET($C$8,0,0,COUNTA($C:$C),1)
> >
> > that works when the range is in one column.
> >
> > what do i change if i want to expand the range to say, AF?
> >
> > I have tried
> >
> > =OFFSET($C$8:$AF$8,0,0,COUNTA($C:$C),1)
> > and
> > =OFFSET($C$8,0,0,COUNTA($C:$AF),1)
> >
> > but neither seemed to work. what am i missing?
> >
> > thanks!

hmmmm - i see what you mean, but i don't quite understand why the width would
get too big. i do see that it is much easier to just count the headers - but
i was thinking that it would be the same as counting C:AF

my headers are in row 7, the data i'll actually be using starts in row 8
from C:AF

i'll try your version and go from there

thanks for the help!

J

"keepITcool" wrote:

>
>
> always a good idea to check help. (first?
> however you've got it almost right..
>
> the width will get TOO big and the formula will be too slow.
>
> you'll need as wide as the number of headers, correct?
> assuming those are in row 8..
>
> =OFFSET($C$8,0,0,COUNTA($C:$C),COUNTA($8:$8))
>
>
>
>
> --
> keepITcool
> | www.XLsupport.com | keepITcool chello nl | amsterdam
>
>
> Gixxer_J_97 wrote :
>
> > and as usual as soon as i post i look at the help for Offset.....
> >
> > =OFFSET($C$8,0,0,COUNTA($C:$C),COUNTA($C:$AF))
> > gives you both height and width
> >
> >
> >
> > "Gixxer_J_97" wrote:
> >
> > > I am trying to create a dynamic range.
> > >
> > > so far i have had luck with the suggestion form another user to use:
> > >
> > > =OFFSET($C$8,0,0,COUNTA($C:$C),1)
> > >
> > > that works when the range is in one column.
> > >
> > > what do i change if i want to expand the range to say, AF?
> > >
> > > I have tried
> > >
> > > =OFFSET($C$8:$AF$8,0,0,COUNTA($C:$C),1)
> > > and
> > > =OFFSET($C$8,0,0,COUNTA($C:$AF),1)
> > >
> > > but neither seemed to work. what am i missing?
> > >
> > > thanks!
>

counta(c:af) counts all filled cells in all rows in the range.
so the width might be a bit bigger than you bargained for.

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Gixxer_J_97 wrote :

> hmmmm - i see what you mean, but i don't quite understand why the
> width would get too big. i do see that it is much easier to just
> count the headers - but i was thinking that it would be the same as
> counting C:AF
>
> my headers are in row 7, the data i'll actually be using starts in
> row 8 from C:AF
>
> i'll try your version and go from there
>
> thanks for the help!
>
> J
>
> "keepITcool" wrote:
>
> >
> >
> > always a good idea to check help. (first?
> > however you've got it almost right..
> >
> > the width will get TOO big and the formula will be too slow.
> >
> > you'll need as wide as the number of headers, correct?
> > assuming those are in row 8..
> >
> > =OFFSET($C$8,0,0,COUNTA($C:$C),COUNTA($8:$8))
> >
> >
> >
> >
> > --
> > keepITcool
> > > www.XLsupport.com | keepITcool chello nl | amsterdam
> >
> >
> > Gixxer_J_97 wrote :
> >
> > > and as usual as soon as i post i look at the help for Offset.....
> > >
> > > =OFFSET($C$8,0,0,COUNTA($C:$C),COUNTA($C:$AF))
> > > gives you both height and width
> > >
> > >
> > >
> > > "Gixxer_J_97" wrote:
> > >
> > > > I am trying to create a dynamic range.
> > > >
> > > > so far i have had luck with the suggestion form another user to
> > > > use:
> > > >
> > > > =OFFSET($C$8,0,0,COUNTA($C:$C),1)
> > > >
> > > > that works when the range is in one column.
> > > >
> > > > what do i change if i want to expand the range to say, AF?
> > > >
> > > > I have tried
> > > >
> > > > =OFFSET($C$8:$AF$8,0,0,COUNTA($C:$C),1)
> > > > and
> > > > =OFFSET($C$8,0,0,COUNTA($C:$AF),1)
> > > >
> > > > but neither seemed to work. what am i missing?
> > > >
> > > > thanks!
> >

now that makes perfect sense!

thanks!

J

"keepITcool" wrote:

> counta(c:af) counts all filled cells in all rows in the range.
> so the width might be a bit bigger than you bargained for.
>
> --
> keepITcool
> | www.XLsupport.com | keepITcool chello nl | amsterdam
>
>
> Gixxer_J_97 wrote :
>
> > hmmmm - i see what you mean, but i don't quite understand why the
> > width would get too big. i do see that it is much easier to just
> > count the headers - but i was thinking that it would be the same as
> > counting C:AF
> >
> > my headers are in row 7, the data i'll actually be using starts in
> > row 8 from C:AF
> >
> > i'll try your version and go from there
> >
> > thanks for the help!
> >
> > J
> >
> > "keepITcool" wrote:
> >
> > >
> > >
> > > always a good idea to check help. (first?
> > > however you've got it almost right..
> > >
> > > the width will get TOO big and the formula will be too slow.
> > >
> > > you'll need as wide as the number of headers, correct?
> > > assuming those are in row 8..
> > >
> > > =OFFSET($C$8,0,0,COUNTA($C:$C),COUNTA($8:$8))
> > >
> > >
> > >
> > >
> > > --
> > > keepITcool
> > > > www.XLsupport.com | keepITcool chello nl | amsterdam
> > >
> > >
> > > Gixxer_J_97 wrote :
> > >
> > > > and as usual as soon as i post i look at the help for Offset.....
> > > >
> > > > =OFFSET($C$8,0,0,COUNTA($C:$C),COUNTA($C:$AF))
> > > > gives you both height and width
> > > >
> > > >
> > > >
> > > > "Gixxer_J_97" wrote:
> > > >
> > > > > I am trying to create a dynamic range.
> > > > >
> > > > > so far i have had luck with the suggestion form another user to
> > > > > use:
> > > > >
> > > > > =OFFSET($C$8,0,0,COUNTA($C:$C),1)
> > > > >
> > > > > that works when the range is in one column.
> > > > >
> > > > > what do i change if i want to expand the range to say, AF?
> > > > >
> > > > > I have tried
> > > > >
> > > > > =OFFSET($C$8:$AF$8,0,0,COUNTA($C:$C),1)
> > > > > and
> > > > > =OFFSET($C$8,0,0,COUNTA($C:$AF),1)
> > > > >
> > > > > but neither seemed to work. what am i missing?
> > > > >
> > > > > thanks!
> > >
>