Dragging Cell References/Formulas

[Richhall]

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

 

[Bob Phillips]

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)

 

[Bernard Liengme]

In A1 use =OFFSET(Sheet2!BA$1,0,ROW(A1)-1) and copy down the column
Do likewise in other columns
best wishes

 

[Max]

Another way, place in A11
=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 A11, copy down

 

[Richhall]

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

Cheers

Rich

 

[Max]

.. 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.