# Dynamic Named Range that expands across, not down ??

• Hi All,

I know how to write the standard dynamic range that expands down:
=offset(\$A\$1,0,0,counta(\$A:\$A))...
But I want my range to expand across and not down, I tried
=offset(\$A\$1,0,0,,counta(\$1:\$1)) but it's not working ????

Any thoughts on this??

Puzzled in Connecticut...

Joe

Joseph Dowski
Woodbury, CT. USA.

• try this:

=OFFSET(Sheet1!\$A\$1,0,0,1,COUNTA(Sheet1!\$1:\$1))

• might try;

=OFFSET(A1,0,0,1,COUNTA(ROW(1:1)))

• if you happen to know if that range, or the last element in the range, is going to be text or numeric, try either of the following :

text :

=OFFSET(Sheet1!\$A\$1,0,0,1,MATCH("zzzzz",Sheet1!\$1:\$1))

numeric :

=OFFSET(Sheet1!\$A\$1,0,0,1,MATCH(999999999999999,Sheet1!\$1:\$1))

• Hi Chris:

Your formulation is to cover if the entries in row 1 may be non-contiguous -- for contiguous entries btadams and bnix's formulations should also work -- right?

Regards!
Yogi Anand

• Hi Joe

• Quote

Originally posted by Yogi Anand
Hi Chris:

Your formulation is to cover if the entries in row 1 may be non-contiguous -- for contiguous entries btadams and bnix's formulations should also work -- right?

Yogi,

of course....

B+ for you

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!