Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Transpose link (Excel 97)

    If I have data in cells F5-J5 in Sheet1 and I want to link them to Sheet2 cells B8-B12, as far as I know I can either transpose them, but then I paste the actual data, or link them, but then I have to do it cell by cell. Is there any way to link the data to the second sheet by automatically transposing it?

    Thanks,
    K.

  2. #2
    Lounger
    Join Date
    Oct 2001
    Location
    Isleworth, Gtr London, England
    Posts
    28
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Transpose link (Excel 97)

    Yes, you can link and transpose at the same time.

    Select cells B8-B12 in sheet2, and type this formula in the active cell ....
    =TRANSPOSE(Sheet1!F5:J5)
    and then press CTRL-SHIFT-ENTER instead of ENTER.

    This creates an array link, using the TRANSPOSE function to change the shape of the link.

    Hope this helps.
    Glenn.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transpose link (Excel 97)

    Hi Glenn,

    It's actually a great way, thanks a lot, I will keep it in mind for the future. Right now I cannot use it, however, because all I want is to make my own life easier and select multiple cells to paste/link. I cannot use the transpose formula in the other sheet, as that goes to a client, and they will be wondering what is that strange formula, when all they need is a link. Also I think if I use an array formula, I cannot modify later single cells within that array, and I might need to do so.
    So I guess I'll just copy/paste link the single cells one by one, although they are so many of them...

    Thanks anyway.

    Bye,
    K.

  4. #4
    Lounger
    Join Date
    Oct 2001
    Location
    Isleworth, Gtr London, England
    Posts
    28
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Transpose link (Excel 97)

    Sorry to hear that you can't use this method.

    One way to speed up the process is to link a single cell, then edit the formula so that is a relative formula, ( i.e. no $ signs ), and then copy the formula rightwards for as many cells as necessary, and afterwards drag each cell so that it goes where you want it to.

    Hope that helps a bit, I've done similar kinds of linking hundreds of times, and using relative formulas, dragging after copying, and using Edit/Replace to change formulas in bulk all save time.

    Glenn.

  5. #5
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transpose link (Excel 97)

    That's ok, what you told me now it does help a bit, I will use it.

    Thanks again for all your help!

    Regards,
    K.

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transpose link (Excel 97)

    You might use the OFFSET function to do this (I assumed this formula to go on cell B8)

    =OFFSET(sheet1!$F$5,0,row()-row($B$8),1,1)

    This formula will adjust itself should any rows/columns be inserted or deleted in either sheet.

    I wouldn't worry too much about the client not understanding you formula's. I guess they pay you for a solution, not for exactly how you've achieved it. Sometimes making it more complicated will impress them and ensure you keep your work (just joking!!)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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