Copy a column and pasting as a row?!

[mikerascally]

How do you make a row equal a column while keeping the formula consistent? I tried using the array command (CTRL-SHIFT-ENTER) but all it does is copy the 1st formula. I also tried using the $ commands but they don't work when you go from column to row or vice versa...why?!. Why would Excel limit itself with this aspect?! How do you do this without getting into code?

 

[xlbo]

what is the formula ??
what cells does it currently reside in ??
what cells do you want it to reside in ??


Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[mikerascally]

No specifics formula or location. Just how do you do the example below using arrays or the $ command
Sheet1
Column D
(row1) =A1+A2
(row2) =B1+B2
(row3) =C1+C2

Sheet2
Column A Column B Column C
(row1){=sheet1!A1+A2} {=sheet1!B1+B2} {=sheet1!C1+C2}

It is basically transposing the data in sheet1 to sheet2 without using the transpose command since it can't copy an array

 

[xlbo]

If you absolutely reference the rows AND columns, you can just copy the data using paste>Special All & Transpose

HOWEVER - it will NOT add a sheet reference to the formula.

1st point - what formula are you expecting ??
=sheet1!A1+A2 (as you have written) OR
=sheet1!A1+sheet1!A2

????????

It's all in the setup of the formula
enter the formulae on sheet1 as :
=sheet1!$A$1+Sheet1!$A$2

and it will copy across using the transpose function in Edit>Pastespecial without a problem. If, however, you do not reference the sheet, how is excel supposed to kow what you want it to do ??

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[mikerascally]

I see. I thought excel would autodetect that it is pasting into another sheet.

Ok Thanks alot Geoff!

 

[xlbo]

Excel DOES detect that BUT you have not specified the sheet in the formula - by using =A1+A2, you are relatively referencing EVERYTHING. By using =$A$1+$A$2, you are ABSOLUTELY referencing the CELLS but RELATIVELY referencing the SHEET. To absoutely reference EVERYTHING ,you must add a sheet qualification as well hence
=Sheet1!$A$1+Sheet1!$A$2 will work when transposed

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[KenWright]

>> without using the transpose command since it can't copy an array

What makes you think that?

With data on Sheet 1 in A1:A10, select A1:J1 on sheet2, type the following and array enter using CTRL+SHIFT+ENTER

=TRANSPOSE(Sheet1!A1:A10)

If successful will appear as

{=TRANSPOSE(Sheet1!A1:A10)}

Another option is simply to use the OFFSET function in conjunction with either ROW() or COLUMN() variables depending on which way you are going. using same data above, in cell A1 on sheet2 put the following and copy across to J1:-

=OFFSET(Sheet1!$A$1,COLUMN()-1,0)

Regards
Ken..............


----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[MrBubbles]

I am new to this forumn and apologize if I am posting this question incorrectly...

I have a Excel loan amortization table (out of box template supplied with Excel) that has interest payments indicated in a column. G18 is the interest for March 06, G19 is the interest for April 06, etc.

On a separate sheet, I have a financial pro-form that has the months in columns... March is column C, April is column D, etc. I want C25(current sheet)= G18 (from the amortization table), D25 = G19, E25 = G20, etc.

How do I copy/paste/transpose this data?

Thank you!

 

[AnotherHiggins]

Welcome to Tek-Tips, MrBubbles!

You just answered your own question.

Copy, Edit > Paste Special > Transpose

And just for future reference - you should create a new thread rather than reviving one from 1.5 years ago.

[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.

 

[MrBubbles]

Sorry about reviving an old thread - I realized shortly thereafter that I should have started a new one.

However, copy, paste, transpose does not do the trick! It pastes the functions (=IF(Pay_Num<>"",Beg_Bal*Interest_Rate/Num_Pmt_Per_Year,"") into the new spread sheet

 

[AnotherHiggins]

No problem on the old thread thing.

->It pastes the functions
Then
Edit > Paste Special > Transpose & Values

[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.