The TRANSPOSE() function really ruined my file

[zjianguk]

Dear all,

It is my headache, and a bit disaster to find that my file has been damaged because of the TRANSPOSE() function. I never came across the problem before, and it is my first time to use this function in a file. Basically, I just inserted a new column next to the TRANSPOSE() function. Suddenly this messed up data in another tab where the TRANSPOSE() function is referred to.

I used the function as: =TRANSPOSE(Sheet2!B8:CZ8)

Can I get your advice, or experience about this function? If this function causes problem, is there a better way to transpose a row into a column?

[Pete_UK]

You can transpose a row into a column using INDEX, or INDIRECT, or OFFSET (and maybe other ways).

However, you have to be careful about inserting or deleting rows or columns when you have active formulae, as these can be severely affected by your actions.

Pete

[zumbalj]

If you copy your data and choose Paste Special instead of regular Paste, there is a checkbox next to "Transpose" that will transpose rows and columns. This is a one-time thing though, won't update when you change the source data, so it depends on how you want to use it.

[zjianguk]

I use a function to get this done. Thanks Pete.

Does anyone get this same experience before for the transpose() function? Cannot understand how Microsoft failed to resolve this bug with so many years of existence.

[efris]

Can you explain what got messed up on the other sheet? The Transpose array doesn't write anything to another location, is it possible that the mesed up cells have formulas refering back to the cells with your Transpose array formulas? Have never had the same experience.

[zumbalj]

I know you use a function to do it: my question to you is, with what you're trying to accomplish, is a function required or is it possibleto copy and paste/transpose instead?