Transposing formula from Row to Column

[StephenA]

I have a series of formula in a row and I want to produce the same in a column on another worksheet.

I thought that thread770-25863 (which uses Notepad)would help but it does not as it increments the Row Number.

Copy, Paste special, Transpose only works on data not formula.

Any solutions?

 

[Clyde38]

I'm not sure how you did your copy - transpose, but it works fine with Excel 2003. Did you copy both the formula and the referenced cells?

 

[StephenA]

I want to leave the referenced cell as it is in the row.

 

[yakpol]

Choose both, "transpose" and "formulas" options in paste special command.

 

[joerd]

A good utility to help you with this is ASAP utilities (

http://www.asap-utilities.com

). They have a "Transpose data with formulas" option.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[StephenA]

Yakpol,

When I do that I get #REF in the cell.

 

[jghrist]

If you want to transpose the formulas but not the referenced cells, make all cell references absolute, e.g. $A$2.

 

[yakpol]

You may want to play with absolute referencing like $A2 or A$2 and transpose formulas first on the same worksheet and just then to move on the other sheet. It will work for sure if if all the formula variables are in the same column.

 

[StephenA]

Thanks for all your help.

The ASAP Utilities is the answer. It works well.