|
OAK's Transpose command copies cells from the one area of the active worksheet to another, rotating their orientation, so that what previously was arranged along rows becomes listed down columns.
Why would you want to use it?
As noted repeatedly in this help, Operis believes strongly that the best way to test a spreadsheet is to prepare reconstructions of the key calculations, in a way that is intentionally different from the original.
One way to build up a parallel calculation that is different from the original is to make a different choice of the dimensions along which to lay it out. In the illustration below, a model under test that has time and revenues/costs as primary categories, with territories as a subcategory, is reconstructed using time and territories as the primary categories, with revenues/costs as the subcategory.
Model under test
|
Reconstruction
|
|
|
As can be seen from the shaded areas, which indicate select cells that are equivalent in the two analyses, the numbers in the reconstruction are at right angles to the original values. Any linkage between the two can be established by
| • | selecting the the original cells in the model under test |
| • | copying them to the clipboard |
| • | switching to the window containing the reconstruction spreadsheet |
| • | using Excel's Paste Link command |
| • | while the newly linked cells are still selected, invoking OAK's Transpose command to flip them through 90 degrees. |
How to use it
To transpose the cells in place
| 1. | Select the cells you want to transpose |
| 2. | Invoke the OAK4 | Range | Transpose command |
| 3. | The selected cells will be transposed. |
To make a transposed copy of the cells at another place
| 1. | Select the cells you want to copy. |
| 2. | Press and hold Ctrl and click on a single cell which will be the top-left corner of the area copied to. (This selects a multi area range of 2 areas). |
| 3. | Invoke the OAK4 | Range | Transpose command. |
| 4. | The contents of the source cells will be copied to a transposed, but otherwise equivalent-sized, rectangle anchored on the indicated position. |
Wrinkles
This command does not work when applied to protected worksheets. You must manually unprotect them first.
The destination cell should be a single cell. It must be blank, since otherwise it is not clear which cell is to be copied. If no destination cell is specified, it is taken to be the top left corner of the selection.
If the formula being transposed acts on a range of several cells and identifies them by user-defined names, it is necessary to replace the names by coordinate references before copying them, since the row/column matching on which such names typically rely is most unlikely to work when the formulas have been transposed. OAK will perform this action. In fact, it will remove the names even when it isn't strictly necessary, for example when a single cell is identified by name.
What you could do if you didn't have OAK
Excel offers its own means to transpose formulas:
| • | Highlight a range and copy it onto the clipboard. |
| • | Select a destination cell. |
| • | Use Excel's Edit | Paste Special | Transpose option. |
But there are several differences between the Excel command and the OAK one which in many cases make the difference between an action that is useful in several circumstances and one that is useful only rarely.
| • | Excel adjusts the formulas so that any relative column references become equivalent relative row references, and vice versa. So, using R1C1 notation, =RC[1] becomes =R[1]C after transposition. The result is that the formulas refer to different cells after they have been transposed, unless those referenced cells have themselves been transposed at the same time. OAK makes no such adjustment, so that although the formulas are transposed, they still refer to the cells that they mentioned before, which is what is wanted if they are devoted to plucking numbers out of another worksheet in order to validate them. (Several tricks for defeating Excel's alteration of these formulas are offered in the discussion of OAK's Copy Literal command) |
| • | The Excel command makes no adjustment to a formula's uses of Excel names. Since such formulas often rely on Excel's column or row matching, they will cease to work after transposition. As already noted, OAK addresses this limitation by stripping out any names, substituting coordinate notation, before doing any transposition. |
| • | An attempt to use Excel's transpose command to transpose cells in place will result in the complaint that "The selection is not valid". Cells have to be transposed to a location that does not overlap the original. OAK stores the cell contents before transposing them, so it is able to overwrite the original cells with the new contents. |
| • | If you copy a small number of cells and paste them into an area consisting of a larger number of cells, Excel's Paste command will repeat the source material as necessary to fill the target area. No equivalent action is possible with OAK's Transpose, as it doesn't offer the opportunity to specify the target area with more than one cell. |
|