Pasting Formulas Horizontally
Amazing chart utilities from Jon Peltier
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Pasting Formulas Horizontally

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Los Angeles, CA
    Posts
    752
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

    Thank Anyway for the suggestion

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Los Angeles, CA
    Posts
    752
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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"


    :: Pharma Z - Family drugstore ::

  6. #6
    BatCoder
    Join Date
    Feb 2002
    Location
    Turkey
    Posts
    799
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  7. #7
    New Member
    Join Date
    Apr 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for the help

  8. #8
    Board Regular
    Join Date
    Apr 2002
    Location
    Cape Town,South Africa
    Posts
    234
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Hi Jenn

    Try Transposing and let us know if it works



User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com