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

4. ## 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. ## 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. ## 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. ## Re: Transposing Links (2003)

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

8. ## 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. ## 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. ## 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. ## 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
•