Turn your worksheet sideways in 1-2-3
Turn your worksheet sideways in 1-2-3
|
Tip Suppose you have a 1-2-3 worksheet laid out like the one shown.  Column E contains formulas like @sum(B2..D2), and Row 6 contains formulas such as @sum(B2..B5). Now suppose your boss wants the worksheet turned sideways, with the row headings displayed across the top and the column headings down the side:  The Range--Transpose command may seem like a good place to start. If you use it, though, you'll find that it converts all the formulas into numbers. Here's the correct way to turn the table: in cell A10, enter the formula @@(@coord (1,@cell("row",A1..A1),@cell("col",A1..A1),8)) and copy it into range A10..F14. You'll end up with a pivoted image of the original table that changes when you update values in the original. The formula uses 1-2-3's @cell functions to retrieve the row and column numbers of the referenced cell. The @coord function converts those numbers into a cell address; I've used the row number where @coord expects to see a column number, and vice versa. Note that the first argument of the @coord function, 1, refers to sheet A; to use it on another worksheet, adjust the number accordingly. Finally, the @@ function creates a reference to the cell address produced by the @coord function. Obvious? No. Handy? Yes! - Richard Scoville | Category: Spreadsheet Issue: Sep 1997 Pages: 170 |
These Web pages are produced by Australian PC World © 1997 IDG Communications