I have a table with several columns of data and need to copy some columns into rows in another table.
—–
James Anthony
MacMost Q&A Forum • View All Forum Questions • Ask a Question
How To Copy Data From a Column To Row In Numbers?
Comments: 5 Responses to “How To Copy Data From a Column To Row In Numbers?”
Comments Closed.
There's no way to copy and paste like that. But you wouldn't want to anyway. If the numbers in the first table change, you would have to repeat the copy and paste.
Instead, use formulas to take the current value of one cell and place it in another. If you are talking about a small set of cells, like 10 or less, then you can just do that with simple formulas. For instance putting =B2 into cell D2 will copy the current value of B2 into D2 and it will update as B2 changes.
If you are talking about a lot of cells, then you can use the OFFSET and COLUMN functions to help. For instance:
=OFFSET($B$2,COLUMN()-4,0)
Put that into D2 and it will take the value of B2. But then copy and paste it into E2 and the value of COLUMN() increases by one so it offsets the row by 1 (5th column minus 4) and you get the value of B3. You can paste it into a series of cells horizontally and get the values from the B column. The trick is to make sure that the initial cell (B2) is set as absolute ($B$2 not B2) so it always counts down from there.
And you should be using multiple tables, so B2 will actually be something more complex as it will refer to the cell in the first table, whereas your new rows will be in a second table.
Have tried typing in the formula as described but I get: OFFSET requires between 3 and 5 arguments but only one was given.
Any Help on that one ?
Thanks
I would have to see your formula to know what could be wrong. Why not copy and paste it into the next comment?
From your answer Gary, I gather Numbers does not have a Transpose command. Of course, even it does, your suggestion that James use formulas has signifiant advantage.
Dick there is a Transpose command/function in Numbers - but it differs from the Excel command/function. Enter "transpose" in the help search window. This one works only with charted data as I understand it.