Transposing

jpearson · Mar 13th 2003

Hello Ozgrid!

I'd like to transpose some cells. If I do it by copy/paste special/transpose, then it works but then I loose the links that the cells have to other sheets. I have 280 sheets and it will take some time to transpose in this manner and then re-link everything. Is there a way to just select the cells and then transpose them directly? I believe I have done this before but I forgot how and I tried to find it in excel help but couldn't. Please fill me in if you know the trick! Thanks! :guitar:

Jeremy

Barrie Davidson · Mar 13th 2003

Quote

Originally posted by jpearson
Hello Ozgrid!

I'd like to transpose some cells. If I do it by copy/paste special/transpose, then it works but then I loose the links that the cells have to other sheets. I have 280 sheets and it will take some time to transpose in this manner and then re-link everything. Is there a way to just select the cells and then transpose them directly? I believe I have done this before but I forgot how and I tried to find it in excel help but couldn't. Please fill me in if you know the trick! Thanks! :guitar:

Jeremy

Try this, select your copy range and then do a find and replace (CTRL+H). Find the equal sign "=" and replace with a plus "+". Then copy that range and transpose to your new range. Select your new range and reverse the process (replace the plus sign with the equal sign).

Hope this helps you out.

Barrie

jpearson · Mar 13th 2003

Thanks Barrie, I'm going to try it out and see if it works!

jpearson · Mar 13th 2003

Thanks for the response Barrie,

It says excel cannot find a match. I don't think this would work because I want to transpose data on sheets 2 through 280 and the cells in sheet one are linked to cells in sheet 2-280 rather than cells in sheet 2-280 linked to sheet1. Isn't there a way to just select the area and transpose without a copy and paste? I imagine not, but maybe there is another trick that may work?

Rennie · Mar 13th 2003

Hi Jeremy,

You could use the offset function to achieve what you want to do.

I have attached a sample for you. Although longwinded this may help for the time being.

If you need more information on how it works email me on [email protected]
Thanks,
Rennie

Anonymous · Mar 13th 2003

Jeremy,

not an answer to your direct question, but just comfort message that if you do have to end up breaking all the links by transposing, we can probably relink to the new transopsed sheets quite easily using =INDIRECT

are your sheet names named logically at all ie they have some sort of 2,3,4,5,6 to 280 in their names somewhere ? And are the links on SHeet1 pretty uniform when they link to each respective sheet ? if both yes, INDIRECT will at least let us relink pretty easily

not the answer you're looking for, but it might be useful if the immediate problem isn't solved..... I'll keep an eye on this thread

Rennie · Mar 13th 2003

Sorry folks, my single brain cell has forgotten how to attach. This is the third attempt.

Anonymous · Mar 13th 2003

rennie,

if it's a sys problem, email it to me and I'll attach it for you mate

(i'd forgotten about OFFSET )

jpearson · Mar 13th 2003

Thanks for the tips fellas. I'll look into them to see what I can learn. Meanwhile I researched macros and made my first one. Holy smokes! It did all the transposing, cell modifications, text wrap changes, and column width changes in a flash, that I need to do for each of the 280 sheets. Now that that will be fast I don't think relinking will take too long because all the 280 cells in sheet one are linked to the same cell, S8, on the remaining 280 sheets, so I'll just need to paste S8 280 times. I'll let you know if I get stuck. Thanks again!

Anonymous · Mar 13th 2003

wow - that's pretty impressive

nice one

Anonymous · Mar 13th 2003

Rennie's attachment for the offset suggestion :

jpearson · Mar 13th 2003

Thanks for the file Rennie! I'll check it out

Jeremy

Transposing

Participate now!

Share