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

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

In A1 use =OFFSET(Sheet2!BA$1,0,ROW(A1)-1) and copy down the column
Do likewise in other columns
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"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

Another way, place in A1:D1
=OFFSET(Sheet2!$BA$1,,ROWS($1:1)-1)
=OFFSET(Sheet2!$BA$9,,ROWS($1:1)-1)
=OFFSET(Sheet2!$BA$3,,ROWS($1:1)-1)
=OFFSET(Sheet2!$BA$6,,ROWS($1:1)-1)
Select A1:D1, copy down
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"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

Thanks Everyone, i just used the $ $ signs, and this is exactly what I
needed. Forgotten about using them.

Cheers

Rich

.. i just used the $ $ signs, and this is exactly what I needed

I'm really confused on your statement above with respect to your original
post. It just doesn't gell. The way you described it in your original post
could only be satisfied using the options in either of the 3 responses that
you received. You must have changed your issue midstream.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---