Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Referencing rows to columns
I would like to know how to make a function that is copied along rows
by clicking the box until the + sign shows and dragging downward to reference columns rather than rows. For example, when copying a formula along the y axis (via the above "click and drag" method so often used by all of us), the formula naturally makes changes such that a reference to A4 becomes A5, A6, A7, A8, ... etc. The reference changes along the y-axis as the formula is copied vertically. However, I need the formula to change from A4 to B4, C4, D4, E4, .. etc as I copy the formula vertically using this same "click and drag" method. Essentially, I'm asking about setting the row constant while allowing only the columns to change AS I'M TRYING TO COPY FORMULAS VERTICALLY. Can the direction of the "click & drag" copy function be set to cross-reference (i.e. changing along the columns as I'm copying along the rows)? Otherwise, I would have to go into each cell after copying and change all the column values. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Referencing rows to columns
Have a look in Excel Help at the OFFSET or INDIRECT functions - both
would allow you to do this. Hope this helps. Pete On Oct 11, 11:02 pm, wrote: I would like to know how to make a function that is copied along rows by clicking the box until the + sign shows and dragging downward to reference columns rather than rows. For example, when copying a formula along the y axis (via the above "click and drag" method so often used by all of us), the formula naturally makes changes such that a reference to A4 becomes A5, A6, A7, A8, ... etc. The reference changes along the y-axis as the formula is copied vertically. However, I need the formula to change from A4 to B4, C4, D4, E4, .. etc as I copy the formula vertically using this same "click and drag" method. Essentially, I'm asking about setting the row constant while allowing only the columns to change AS I'M TRYING TO COPY FORMULAS VERTICALLY. Can the direction of the "click & drag" copy function be set to cross-reference (i.e. changing along the columns as I'm copying along the rows)? Otherwise, I would have to go into each cell after copying and change all the column values. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Referencing rows to columns
The row remains constant when you copy the formula across the row with click
and drag. A4 becomes B4 which becomes C4, etc. Can you show us your formula in a cell like A1 and show us what you want the copy of it to be in cell A2? wrote in message ups.com... I would like to know how to make a function that is copied along rows by clicking the box until the + sign shows and dragging downward to reference columns rather than rows. For example, when copying a formula along the y axis (via the above "click and drag" method so often used by all of us), the formula naturally makes changes such that a reference to A4 becomes A5, A6, A7, A8, ... etc. The reference changes along the y-axis as the formula is copied vertically. However, I need the formula to change from A4 to B4, C4, D4, E4, .. etc as I copy the formula vertically using this same "click and drag" method. Essentially, I'm asking about setting the row constant while allowing only the columns to change AS I'M TRYING TO COPY FORMULAS VERTICALLY. Can the direction of the "click & drag" copy function be set to cross-reference (i.e. changing along the columns as I'm copying along the rows)? Otherwise, I would have to go into each cell after copying and change all the column values. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Referencing rows to columns
Since you didn't give any examples of your formulas, this will do as you
asked for the individual cells that you did mention. Enter this in any cell and drag down to copy, and this will copy Row 4 *down* a column: =INDEX($4:$4,ROWS($1:1)) If you need help adapting this to your formulas, post back. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- wrote in message ups.com... I would like to know how to make a function that is copied along rows by clicking the box until the + sign shows and dragging downward to reference columns rather than rows. For example, when copying a formula along the y axis (via the above "click and drag" method so often used by all of us), the formula naturally makes changes such that a reference to A4 becomes A5, A6, A7, A8, ... etc. The reference changes along the y-axis as the formula is copied vertically. However, I need the formula to change from A4 to B4, C4, D4, E4, .. etc as I copy the formula vertically using this same "click and drag" method. Essentially, I'm asking about setting the row constant while allowing only the columns to change AS I'M TRYING TO COPY FORMULAS VERTICALLY. Can the direction of the "click & drag" copy function be set to cross-reference (i.e. changing along the columns as I'm copying along the rows)? Otherwise, I would have to go into each cell after copying and change all the column values. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell Referencing rows to columns | Excel Discussion (Misc queries) | |||
Referencing columns to rows | Excel Worksheet Functions | |||
Referencing columns as rows | Excel Discussion (Misc queries) | |||
Referencing a range of columns and rows with the IF function | Excel Worksheet Functions | |||
Referencing Columns in a Row. PLEASE HELP!! | Excel Worksheet Functions |