Pasting Formulas Horizontally

JennJenn · Apr 11th, 2002, 03:43 PM

Hello,
I hope someone can help me. I have data in a worksheet going vertically. That I need to link to another spreadsheet where the data will be going horizantally (a calendar) everytime I try to use the autofill it changes the column. Ex. I want the data to past links B2, B3, B4, B5, but when I use the fill it changes to B2, C2, D2.

Help!!!

Parra · Apr 11th, 2002, 03:50 PM

Try this, let me know if it works.

Copy, Edit, Paste Special and choose Transpose and paste link.

Use paste link if you want to link the info, if not just choose transpose and click ok.

JennJenn · Apr 11th, 2002, 03:53 PM

This does not work. I tried. When you paste link you lose the option to transpose.

Thank Anyway for the suggestion

Parra · Apr 11th, 2002, 04:02 PM

Another thing you can do depending on the amount of copying and pasting is you can copy and paste link one cell at a time.

It will take time, but it will get the job done.

Chris Davison · Apr 13th, 2002, 05:47 AM

Quote:

On 2002-04-11 14:43, JennJenn wrote:
Hello,
I hope someone can help me. I have data in a worksheet going vertically. That I need to link to another spreadsheet where the data will be going horizantally (a calendar) everytime I try to use the autofill it changes the column. Ex. I want the data to past links B2, B3, B4, B5, but when I use the fill it changes to B2, C2, D2.

Help!!!

using your examples above, try the following :

=INDIRECT("B"&COLUMN(B:B)) and copy paste horizontally

the "B" will remain static, whilst the COLUMN command returns the column number of "2" to give "B2"

pasted over will increment the column to C:C which is "3" to give "B3"

smozgur · Apr 13th, 2002, 06:35 AM

Say your formulas in C Column like below

ColC
=B1
=B2
=B3
=B4
=B5
=B6

Goto Sheet2 A1 and select A1 to A6 (A6 for 6 cell, more for more source cell)

Click Paste Function (fx) button on toolbar and select Lookup&Reference type functions then select TRANSPOSE function in it. And as the parameter select C1 to C6 from Sheet1. Then press enter at the same time you pressing Ctrl+Shift (means Ctrl+Shift+Enter).

You got it.

Regards

(edited for (fx) to clarify Paste Function button)

_________________
Oz ~ TheWordExpert

[ This Message was edited by: smozgur on 2002-04-13 05:38 ]

JennJenn · Apr 13th, 2002, 07:44 AM

Thanks for the help

gareth · Apr 24th, 2002, 12:18 AM

Hi Jenn

Try Transposing and let us know if it works

Apr 24th, 2002, 12:18 AM	#8
gareth Board Regular Join Date: Apr 2002 Location: Cape Town,South Africa Posts: 234	Hi Jenn Try Transposing and let us know if it works __________________ Regards Gareth Visit The Excel Maniacs :<>: Have a Chat! :<>: Free Stuff!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 11th, 2002, 03:43 PM	#1
JennJenn New Member Join Date: Apr 2002 Posts: 5	Hello, I hope someone can help me. I have data in a worksheet going vertically. That I need to link to another spreadsheet where the data will be going horizantally (a calendar) everytime I try to use the autofill it changes the column. Ex. I want the data to past links B2, B3, B4, B5, but when I use the fill it changes to B2, C2, D2. Help!!!

Apr 11th, 2002, 03:50 PM	#2
Parra Board Regular Join Date: Feb 2002 Location: Los Angeles, CA Posts: 752	Try this, let me know if it works. Copy, Edit, Paste Special and choose Transpose and paste link. Use paste link if you want to link the info, if not just choose transpose and click ok.

Apr 11th, 2002, 03:53 PM	#3
JennJenn New Member Join Date: Apr 2002 Posts: 5	This does not work. I tried. When you paste link you lose the option to transpose. Thank Anyway for the suggestion

Apr 11th, 2002, 04:02 PM	#4
Parra Board Regular Join Date: Feb 2002 Location: Los Angeles, CA Posts: 752	Another thing you can do depending on the amount of copying and pasting is you can copy and paste link one cell at a time. It will take time, but it will get the job done.

Apr 13th, 2002, 06:35 AM	#6
smozgur BatCoder Join Date: Feb 2002 Location: Turkey Posts: 764	Say your formulas in C Column like below ColC =B1 =B2 =B3 =B4 =B5 =B6 Goto Sheet2 A1 and select A1 to A6 (A6 for 6 cell, more for more source cell) Click Paste Function (fx) button on toolbar and select Lookup&Reference type functions then select TRANSPOSE function in it. And as the parameter select C1 to C6 from Sheet1. Then press enter at the same time you pressing Ctrl+Shift (means Ctrl+Shift+Enter). You got it. Regards (edited for (fx) to clarify Paste Function button) _________________ Oz ~ TheWordExpert [ This Message was edited by: smozgur on 2002-04-13 05:38 ]

Apr 13th, 2002, 07:44 AM	#7
JennJenn New Member Join Date: Apr 2002 Posts: 5	Thanks for the help