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.

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

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.

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.


--
mphell0
------------------------------------------------------------------------
mphell0's Profile: http://www.excelforum.com/member.php...o&userid=30153
View this thread: http://www.excelforum.com/showthread...hreadid=505402

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.

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

Excel 2007 -
I've just been training my daughter in Excel on her new laptop but I am only
used to Excel 2003.
I tried to show her how to copy the Autosum from one column across to the
rest of the columns by dragging the handle - but with 2007 - it copies the
figure in the total box/cell, not the formulae! I am exasperated,
Can someone help please?
thank you

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