Try

=INDEX(Sheet2!$BA$1:$IV$1000,1,ROW(A1))
=INDEX(Sheet2!$BA$1:$IV$1000,9,ROW(A1))
=INDEX(Sheet2!$BA$1:$IV$1000,3,ROW(A1))
=INDEX(Sheet2!$BA$1:$IV$1000,6,ROW(A1))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Richhall" wrote in message
...
Hi

I have a reference in the first line of my spreadsheet on a sheet
referring to another sheet i.e

A B
C D
1 =Sheet2!BA1 =Sheet2!BA9 =Sheet2!BA3
=Sheet2!BA6
2
3
4

If a highlight A1 to D1 and paste into A2 to D2 , or drag down, the
formulas go to:

=Sheet2!BA2 =Sheet2!BA10 =Sheet2!BA4
=Sheet2!BA7

I actually want them to go to:

=Sheet2!BB1 =Sheet2!BB9 =Sheet2!BB3
=Sheet2!BB6

and in row 3 to:

=Sheet2!BC1 =Sheet2!BC9 =Sheet2!BC3
=Sheet2!BC6


So, I wanted to just paste a replica of Row 1 into Row 2, and ill
just do a find and replace of BA with BC, unfortunately it doesn't
seem to want me to do this. How can I do this please?

Cheers

Rich