Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Getting a cell reference to move laterally instead of vertically?
I have data that is sorted in columns, but the data I want to graph is a
cross section of this data and therefore it comes from one row across several columns. I'm trying sort my graph/equation data in a column, however. Is there a way to get the equation cell references to move across a row even while the cell is descending a column? |
#2
|
|||
|
|||
Getting a cell reference to move laterally instead of vertically?
You can do it on a range using the TRANSPOSE function (array entered), or
you may want to take a look at the OFFSET function, which takes a row and column argument. Use the ROW() function to generate the column argument and you will be able to copy the formula down a column and have the references go across a row, eg:- Assuming your data was in E4:L4 and you wanted your formula to start in say A6 and then copy downwards. In A6 use =OFFSET($E$4,0,ROW()-6) and copy down. Note the ROW()-6 bit. When you are in row 6 the ROW() bit gives you 6, whilst the -6 bit turns it into 6-6=0. Therefore the first column offset argument is 0. When you move to row 7 the ROW() bit gives you 7, whilst the -6 bit turns it into 7-6=1, so the column argument is 1 etc -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------*------------------------------*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------*------------------------------*---------------- "Melonhead" wrote in message ... I have data that is sorted in columns, but the data I want to graph is a cross section of this data and therefore it comes from one row across several columns. I'm trying sort my graph/equation data in a column, however. Is there a way to get the equation cell references to move across a row even while the cell is descending a column? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Maintaining cell reference after sorting | Excel Discussion (Misc queries) | |||
Getting contents of a cell when cell reference is in the sheet | Excel Discussion (Misc queries) | |||
Input cell reference is not valid (One Variable Data Table) | Excel Worksheet Functions | |||
dynamic cell reference within a text string | Excel Worksheet Functions | |||
problem with cell reference in =sum(offset(cell reference,x,y,z,a)). Want cell ref to be variable. | Excel Worksheet Functions |