Transpose link (Excel 97)

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote