Dynamic Named Range that expands across, not down ??

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?

Hi Joe

Just to add to the great help you have already received, have a read of This Page. In particular, the help on the OFFSET Function. It also shows some other Dynamic Ranges.

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