copying formulae along rows.

[Richard Hocking]

Hi, I'm trying to set up a sheet with the formula ='Sheet1'!E2 in the first
cell.
I would like to be able to copy this to all cells along the row, so that it
increases to ='Sheet1'!E3 then to E4... E5... etc etc. I've tried using
absolute $ signs, but only seem to be able to get the number part to increase
when copying down the column, not across the row! And, conversely, only the
letter part increases along the row (E2... F2.. G2 etc) if I don't use the
$ sign. Any ideas gratefully received, otherwise I have nearly 1000 copy /
pastes plus changing numbers to do!!

Thanks, Richard.

[vezerid]

Richard,
A $ before either the letter or the number will keep this part of the
cell reference unchanged when you copy, i.e. it is an *absolute*
reference.

If you want to make a copy of Sheet1!E1:E1000 to Sheet2!A1:A1000, then
your formula in A1:

=Sheet1!E1

should work properly. When you copy to A2, it should change
automatically to =Sheet1!E2. If you copy to B1 instead, it should
change to =Sheet1!F1.

Now, from what you say, you have data in columns but you want to
transfer them to rows, otherwise I don't see how your experimentation
would have proved fruitless. Beware that Excel has 60000+ rows but only
256 columns.

If you want to copy columns to rows you can use:
Select and Edit|Copy
Select first cell of destination and Edit|Paste Special... and click
the Transpose checkbox.

Does this help?
Kostis Vezerides

[Pogue]

The cheat I would do is to copy it down 1,000 rows, then select all 1,000
cells and cop/paste special and then click the transpose button.

Actually I just tried it and it worked, except you can't paste it starting
on the same column you are copying from - move it over 1 column.

"Richard Hocking" wrote:

> Hi, I'm trying to set up a sheet with the formula ='Sheet1'!E2 in the first
> cell.
> I would like to be able to copy this to all cells along the row, so that it
> increases to ='Sheet1'!E3 then to E4... E5... etc etc. I've tried using
> absolute $ signs, but only seem to be able to get the number part to increase
> when copying down the column, not across the row! And, conversely, only the
> letter part increases along the row (E2... F2.. G2 etc) if I don't use the
> $ sign. Any ideas gratefully received, otherwise I have nearly 1000 copy /
> pastes plus changing numbers to do!!
>
> Thanks, Richard.

[mphell0]

Richard-

You can use the TRANSPOSE function

Assume that the values you want copied into row 1 of sheet 2 are in column 1
of sheet 1. Say you have 10 values in cells A1 through A10. On sheet 2 cell
A1 put:

=TRANSPOSE($A$1:$A$10) where $A$1:$A$10 is your actual range

This will have to be entered using ctrl-shift-enter because it is an array function

Copy that formula across the row until you have it in as many columns as you
had rows to begin with. With all of those cells highlighted hit F2 and then
ctrl-shift-enter and the values will be pasted. Any changes you make in
sheet 1 will be reflected in sheet 2.

Is this what you are looking for? I hope I explained it well enough.

[Richard Hocking]

Just had a play around, it works! Great stuff, you've saved me hours!

Cheers,

Richard.

"Pogue" wrote:

> The cheat I would do is to copy it down 1,000 rows, then select all 1,000
> cells and cop/paste special and then click the transpose button.
>
> Actually I just tried it and it worked, except you can't paste it starting
> on the same column you are copying from - move it over 1 column.
>
> "Richard Hocking" wrote:
>
> > Hi, I'm trying to set up a sheet with the formula ='Sheet1'!E2 in the first
> > cell.
> > I would like to be able to copy this to all cells along the row, so that it
> > increases to ='Sheet1'!E3 then to E4... E5... etc etc. I've tried using
> > absolute $ signs, but only seem to be able to get the number part to increase
> > when copying down the column, not across the row! And, conversely, only the
> > letter part increases along the row (E2... F2.. G2 etc) if I don't use the
> > $ sign. Any ideas gratefully received, otherwise I have nearly 1000 copy /
> > pastes plus changing numbers to do!!
> >
> > Thanks, Richard.

[Gord Dibben]

I am baffled.

How can you Transpose 1000 rows to 256 columns?


Gord Dibben MS Excel MVP

On Fri, 27 Jan 2006 01:15:03 -0800, Richard Hocking
<[email protected]> wrote:

>Just had a play around, it works! Great stuff, you've saved me hours!
>
>Cheers,
>
>Richard.
>
>"Pogue" wrote:
>
>> The cheat I would do is to copy it down 1,000 rows, then select all 1,000
>> cells and cop/paste special and then click the transpose button.
>>
>> Actually I just tried it and it worked, except you can't paste it starting
>> on the same column you are copying from - move it over 1 column.
>>
>> "Richard Hocking" wrote:
>>
>> > Hi, I'm trying to set up a sheet with the formula ='Sheet1'!E2 in the first
>> > cell.
>> > I would like to be able to copy this to all cells along the row, so that it
>> > increases to ='Sheet1'!E3 then to E4... E5... etc etc. I've tried using
>> > absolute $ signs, but only seem to be able to get the number part to increase
>> > when copying down the column, not across the row! And, conversely, only the
>> > letter part increases along the row (E2... F2.. G2 etc) if I don't use the
>> > $ sign. Any ideas gratefully received, otherwise I have nearly 1000 copy /
>> > pastes plus changing numbers to do!!
>> >
>> > Thanks, Richard.