I have a reference to another sheet and I need to copy the reference like this:
sheet 2
A B
1 ='sheet1'B1 ='sheet1'B2
2 ='sheet1'E1 ='sheet1'E2

if I try to copy/paste or fill I get b1, b2, b3 going down which I don't
want - I know I can do this manually but I have about 100 rows each approx 10
columns to fill like this.

Put this in Sheet2 A1 and copy across as far as needed then copy down

--

Regards,

Peo Sjoblom

"Kia" wrote in message
...
I have a reference to another sheet and I need to copy the reference like
this:
sheet 2
A B
1 ='sheet1'B1 ='sheet1'B2
2 ='sheet1'E1 ='sheet1'E2

if I try to copy/paste or fill I get b1, b2, b3 going down which I don't
want - I know I can do this manually but I have about 100 rows each approx
10
columns to fill like this.

Oops!

=OFFSET(Sheet1!$B$1,COLUMNS($A$1:A1)-1,ROW(1:1)*3-3)
--

Regards,

Peo Sjoblom

"Peo Sjoblom" wrote in message
...
Put this in Sheet2 A1 and copy across as far as needed then copy down

--

Regards,

Peo Sjoblom

"Kia" wrote in message
...
I have a reference to another sheet and I need to copy the reference
like
this:
sheet 2
A B
1 ='sheet1'B1 ='sheet1'B2
2 ='sheet1'E1 ='sheet1'E2

if I try to copy/paste or fill I get b1, b2, b3 going down which I don't
want - I know I can do this manually but I have about 100 rows each
approx
10
columns to fill like this.

Thank you for the super speedy reply.
that solved my needs
much appreciated