Getting a cell reference to move laterally instead of vertically?

#1 11-01-2005, 04:09 PM

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 11-02-2005, 06:17 PM

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" <Melonhead@discussions.microsoft.com> wrote in message
news:EDAE665E-E034-4359-BFA5-FAF52F4F237A@microsoft.com...
>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?

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)