Reference columns to rows
Posted by Augie on March 20, 2001 9:30 AM
How do I reference columns of cells containing data that needs to be laid out on another sheet in rows? I dont want to copy and (transpose) paste the data.
Thanks in advance.. Augie
Posted by Ian on March 20, 2001 10:49 AM
One way
This is a little convuluted, but assuming your column starts cell A1 of Sheet1 you could do the following on Sheet2
In A1 put 1. In B1 put =1+A1. Copy this as far as you need to
In B1 put "QSheet1!A". Copy this as far as you need to.
In C1 put =B1&A1. Copy as far as you need.
Copy and Paste Special the value of row C.
Highlight row C and find Q and replace with =
Delete rows a and b.
There must be a better way.
Posted by Ian on March 20, 2001 10:55 AM
Correction
Correction:
sorry, in C1, put =A2&A1. good luck
Posted by Aladin Akyurek on March 20, 2001 11:14 AM
Another way...
Assuming that your values are in A on Sheet1.
Select the range of these values and name it e.g. SOURCE via the Name Box.
Determine how many values you have in SOURCE (say, 4).
On Sheet2 enter the following formula in A1:
=TRANSPOSE(SOURCE)
While still in A1, select required number of the adjacent cells (in case of 4 items, the selection would contain A1:D1), go to the formula bar where you see the TRANSPOSE formula, and hit CONTROL+SHIFT+ENTER at the same time.
Aladin
Posted by Ian on March 20, 2001 12:28 PM
aka a better way. Very cool.
.
