in your range use something like

=OFFSET($A$1,,,COUNTA($A:$A),1)

not

=OFFSET(A1,,,COUNTA(A:A),1)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"ym" wrote in message
...
thanks for your reply.

but could u elaborate further.
by the way i am using a dynamic named range
if it cant be done with dynamic, its ok if i try with a static one

"Bob Phillips" wrote:

Make sure that the named range references are absolute.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"ym" wrote in message
...
i have a list of cells in sheet1 that i want to be automatically
copied to
sheet2.
what i did was to use named range for the cells and use the forumla
=namedRange in sheet 2.

However it did not paste the entire range. Say the sheet1 cells are
A1:A5.
If i wrote the foruma =namedNamed in sheet2 cell C2, it will paste the
value
relative to the row which is A2 in sheet1. So if i write that forumla
in
C10,
i get nothing.

my ultimate goal would be to be able to copy the range of cells from
sheet1
to sheet 2 automatically. thanks