Results 1 to 11 of 11
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Transposing Links (2003)

    Is there an easy way to transpose a series of links to another folder? That is, if I have a formula in cells A1 to A10 in workbook 2, is there an easy way to transpose those formulas to A! through J1 in workbook 1?
    Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Transposing Links (2003)

    Depends - does Copy followed by Paste Special with the Transpose option do what you want? If not, you'll have to provided detailed information.

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Transposing Links (2003)

    Thanks, Hans. I have attached a file that hopefully will help. What I want to do is copy the formulas for interest portions of payments from Sheet 1 in a column to Sheet 2 in a row. I tried your suggestion, but it didn't seem to work.
    Thanks again.
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Transposing Links (2003)

    You can select the entire used range on Sheet1 (A118), copy it, then paste special with the Transpose option on Sheet2. The formulas will keep on working.

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Transposing Links (2003)

    Not quite what I am after: all I want on Sheet 2 is the Interest portion, not the complete sheet 1 data.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Transposing Links (2003)

    You can enter the following formula in Sheet2!B2:

    =OFFSET(Sheet1!$C$5,COLUMN()-2,0)

    and fill right to M2.

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Transposing Links (2003)

    Excellent!
    Thanks, Hans!
    Just for my education, would you mind explaining how this works?
    Thanks again.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Transposing Links (2003)

    =OFFSET(Sheet1!$C$5,COLUMN()-2,0)

    COLUMN() returns the column number of the cell containing the formula, so for B2 it returns 2, for C2 it returns 3, etc.
    COLUMN()-2 is 0 for B2, 1 for C2, etc.
    OFFSET refers to the cell that is shifted COLUMN()-2 rows down and 0 columns to the right of Sheet1!$C$5.
    So in B2 it refers to Sheet1!$C$5, in C2 to Sheet1!$C$6 etc.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Transposing Links (2003)

    Another possibility:
    - Select B2:M2 on Sheet2.
    - Enter the formula

    =TRANSPOSE(Sheet1!C5:C16)

    - Confirm with Ctrl+Shift+Enter to make it an array formula.

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Transposing Links (2003)

    Thanks again, Hans. One final question: is there a transpose formula or vba that would transpose non-contiguous cells, say A1, C1, E1, G1 from worksheet 1 to A1:A4 on worksheet 2?
    Thanks again.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Transposing Links (2003)

    Simply use individual formulas for each cell.

Posting Permissions

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