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!

named range / offset
 

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!

named range / offset
 

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!

named range / offset
 

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!

named range / offset
 

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!

named range / offset
 

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!