I have a chart I need to make with 115 names down one side and the same 115 names across the top. So A2 and B1 should be the same text, A3 and C1 should be the same text, etc.
I know I can put "=A2" in B1 to make it the same. But if I copy that to C1, instead of giving me "=A2" it gives me "=B2".
Is there a way to make the information in the first column and the first row the same without typing the formula in by hand?
I'm use OpenOffice, I don't even have Excel, BUT . . .
Is this a one time thing, or do you need a formula?
If it is a one time thing I just did this in OpenOffice by . . .
1. Entered five names down the A column, highlight and copy
2. Select the B2 column
3. Edit -> Paste Special -> and selected the "Transpose" option.
That doesn't make a formula that will automatically change one if you change another, but it is a way to copy a vertical column to a horizontal row. (at least in OpenOffice, I assume Excel is similar)
-- Edited by WebGuy on Monday 28th of June 2010 12:45:43 PM
It's not a perfect solution, but I can live with it. I'll just have to copy/paste any time I add another category. It's still faster than typing everything twice.
Wait, I wonder... Nope, darn that didn't work. I thought maybe if I put "=A2" in B2, copied that down the column, then transpose-pasted it into the row that would work, but all I get is formula errors.
Because of going vertical to horizontal, I don't know if there is a simple solution. Normally the $ helps "lock" a cell. I can't seem to make it work for what you are trying to do here.
Let me try something else.
__________________
Stop trying to be what you see. Be what you ought to be.
The "OFFSET" command selects a cell based on reference to other cells
For example
OFFSET(A1; 3; 4)
gives us the info that is 3 cells down and 4 cells over from A1
So, in my formula,
The "$" means absolute, that tells it to not change the "A" column in the formula when copying the formula to another location, so we will always get data from the "A" column.
the "COMUMN()" command gives us the number of this column our formula is in.
so the formula means, give us the results that come from:
Starting at cell A1 . . . then go down one less number of rows than this column . . . and over zero columns.
So, if the formula is in the 12th column, the formula asks us for the data that is in the cell ...
starting from A1, go down (12 - 1) rows and over 0 columns
Hmm, The negatives are supposed to work. A positive is down and right where a negative is for up and left. There may be a better way, I just used the -1 as a way to make it pull the right value.
I'd be surprised if Excel is that much different from OpenOffice on a formula like that.
I was okay with the commas. I knew Excel doesn't do semicolons in formulas, but I tried it first the way you wrote it. I also got errors. I made the switch and we were good to go.
__________________
Stop trying to be what you see. Be what you ought to be.
