COPY FROM COLUMN TO ROW

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have two worksheets set up, one has data entered weekly in a row. i need
to copy this data to the other worksheets by column. my question is, i have
the first column going to the first row in the correct places. Right now if
i drag it down cell B118 becomes B119 in the first column, is there a way to
make it go to C118 instead of changing the 118 to 119 when i drag it down? I
tried using B$118 but then i still get B118 i need it to read C118. I have
52 rows i need to drag down and do not feal like doing each one individually.

Thanks!
 
Hilight the row, copy, then select the first cell in a blank column, right
click, paste spectial, and check the Transpose box at the bottom
 
I am not transposing my row to the exact columns, my rows are in different
columns, i jsut need to now if there is a way to drag down the rows with it
changing the column number instead of row number
 
=INDEX($118:$118,ROWS($118:118))

Entered in a cell and copied down.


Gord Dibben MS Excel MVP
 
The $ symbol denotes an absolute reference following. This means
that, if you are entering B$118 (where B is column and 118 is row),
118 will stay the same (absolute), but the column will change
depending on how many rows it moved.

For instance, if you have =B$118 entered in cell and copy/paste (not
cut/paste) this cell into a cell 6 columns to the right, it will
change to =H$118. If, after doing that, you copy/paste this new cell
down 10 rows, it will remain H$118. Corrollarily, if you have a
reference (that you're dragging down) in row 118, and it reads 119,
chances are you started dragging it somewhere such as row 2, with the
reference being to a cell in row 3.'

It seems like, from what I can understand of your description of the
problem, you have to backtrack to the original formula you're
dragging, and seeing whether it refers to the proper row. Otherwise,
all your data based on the cells in this column will be off by a row.
 
=OFFSET($B$118,0,ROW()-ROW(first_ref)) where first_ref is the absolute
reference of the cell in which you have the formula pointing to B118.
 
Back
Top