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?
0
Melonhead (1)
11/1/2005 7:30:07 PM
excel.charting 18370 articles. 0 followers. Follow

1 Replies
625 Views

Similar Articles

[PageSpeed] 17

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? 


0
ken.wright (2489)
11/2/2005 8:04:58 PM
Reply:

Similar Artilces:

Can you insert a picture into a specific cell?
Or do they only "float" on the page. Marc They float over the worksheet, but you can adjust the size so that it seems to fit a cell exactly. If you insert a picture, you can hold the alt-key down while you move/resize the picture. When it gets close to the edge of a cell, it'll snap-to that edge. Marc wrote: > > Or do they only "float" on the page. > > Marc -- Dave Peterson ...

Opening publisher 97 with a later version
I have publisher 97 on my windows xp and it works fine. However, I had someone refine some work I had done and apparently they used a later version of Publisher because when I loaded their CD, I got the message "Publisher cannot load files from a different version" What can I do--does this mean I have to buy a later version to match the version he used, or do I have to buy one of those programs that can open other programs. If I do the later, will I be able to use my 97 version to make changes once I get the files open on the later version Thanks rjda Refer to http://www.mvps....

I cannot get the pictures to show on screen in Publisher web previ
I have tried embedding and linked but nothing seems to work. Do I have to apply a master page? It is to be a one page web site. This is probably so basic but I am trying to teach myself how to use it. What do I have to do to have them show in preview and once uploaded? Thank you. Are you using FireFox to preview or IE? DavidF "rendul" <rendul@discussions.microsoft.com> wrote in message news:C9B0A640-6C87-4E9A-8A0E-5F32F7DAC0DE@microsoft.com... >I have tried embedding and linked but nothing seems to work. Do I have to > apply a master page? It is to be a one ...

Joining text with a formula in cell #4
just to complete the thread... I found the answer. You have to change the format of the cell to custom 0.00"*" this is the only way it will show only 2 decimal places Thanks for the hel -- Mustard Hea ----------------------------------------------------------------------- Mustard Head's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1630 View this thread: http://www.excelforum.com/showthread.php?threadid=27700 ...

Outlook rule to move read message from Blackberry
I have Outlook configured with a Blackberry server and want to set up a rule to mange messages marked as read: For example. 1. I'm away from the office, Outlook is closed. 2. I read an email on my Blackberry, which marks the message "read" 3. When I return to the office and open Outlook I want to: a. Apply a rule to my inbox that states: If message is marked as read, move to folder "read messages" b. All unread messages will remain in the inbox. I'm a heavy user of rules but I can't find a way to identify "read" messages. Any thoughts? view360@gm...

Single click selects multiple cells
When clicking on a single cell multiple cells are selected. The one time solution for this is to zoom in or out. This is problematic as 60% seems to be the zoom that works most of the time but at this zoom level the cell contents do not display. The time lost and the frustration that builds is killing my productivity and office attitude. Please give all of us a permanet fix. -- Thanks Mike ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" butt...

Getting the BCC field by default.
I would like the BCC field to show by default but cannot find the setting for this in 2007. Anyone know where this is located? In a new message window, use the Options chunk to display the "Show BCC." --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. After furious head scratching, JC HARRIS asked: | I would like the BCC field to show by default but cannot find the | setting for this in 2007. Anyone know where this is located? Thanks Milly. I had...

Referencing cells across sheets
Hi, I've created a workbook with three sheets. The first three columns of sheet 2 and sheet 3 are referenced to the first three columns of sheet 1. What I'd like to do, is set the workbook up so that if I insert a row before, between or after referenced rows in sheet one, a row will also be inserted, and referenced, in the corresponding place on sheets 2 and 3. Does anyone know how to do this? Also, if no data is entered into a cell on sheet one, the value shown in the corresponding cells of sheets '2' and '3' is '0'. I need those cells to remain bla...

Copying Data in a cell in one sheet to a cell in another sheet
I've run into a problem trying to copy data from a cell in one sheet to another. I have a spreadsheet called "rating" which contains a number of formula that calculates a final number. I also have a spreadsheet called "Final" that copies over the information from "Rating". In "Final", I'm trying to copy a number from "Rating" into a particular cell. I put in =Rating! G89, but it won't work. When I press enter, a window pops up "Update value:Rating". I press enter again and in the cell where I want the number ...

How to unlock locked cells by MACRO?!
I have locked Sheet and I need to change particular cells. But when my macro wants to change it => ERROR :/ Is there any way to unprotect these particulary cells by macro and change them? - after that action, of course, I want to lock them again. Please help me, Pedro Just unprotect the sheet, do the work, and reprotect the sheet. Kind of like: activesheet.unprotect password:="yourpassword" 'your code to change stuff activesheet.protect password:="yourpassword" Petr N�me�ek wrote: > > I have locked Sheet and I need to change particular cells. But when m...

Showing 3:54PM instead of 3:54:03PM
How do you get rid of the seconds in the time area. I have changed the formatting in the time. I use the excel file as a data source. I include the time in the mail merge. It always shows up with the seconds in the time. Very frustrating. HELP PLEASE!! TJ it may be formatted as text, so won't respond to changing the time format. if it is text, the TIMEVALUE formula will convert it to a decimal-based time value which can then be formatted by using Excel's normal Number formatting-- to get rid of the seconds. Eddie O "TJ" wrote: > How do you get rid of the secon...

How to add a button to restore all altered cells original values?
I want to add a reset button to an excel spreadsheet that will restore the values of all changed cells to the original saved ones. Any help would be appreciated. Thanks Dawn Hi this would require quite some VBA code as you somehow have to store the original values for example on a separate hidden sheet -- Regards Frank Kabel Frankfurt, Germany "Dawnybros" <Dawnybros@discussions.microsoft.com> schrieb im Newsbeitrag news:3340601E-16EE-4296-8F50-B0BAC18EA387@microsoft.com... > I want to add a reset button to an excel spreadsheet that will restore the > values of all ...

Getting rid of selection
How can I get rid of the selection rectangle? It seems that it's always there, with a heavy black rectangle, or there's a light black rectangle marking where it was. I'm trying to get rid of it altogether, so I can capture an image of the sheet for use in a webpage. I can achieve the effect that I want by selecting a cell which is outside the area that I'm trying to capture, but now that I've found that I cannot get rid of it entirely, it is driving me nuts trying to do so. -- Steve Swift http://www.swiftys.org.uk/swifty.html http://www.ringers.org.uk You could al...

Can i use conditional formating on a cell when it contains a formula?
I am trying a "conditional formatting" on a cell that contains formula, but it didn't work. "If cell value is equal to 0 then font - white" This doesn't work, stays always. If i use this condition on a cell without formula it works just fine. Thank -- si ----------------------------------------------------------------------- sit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=262 View this thread: http://www.excelforum.com/showthread.php?threadid=26784 Hi are you sure your formula returns an exact zero?. Could you post the formul...

Workplace Queues
We just rolled out CRM a few weeks ago. I'm getting a lot of complaints from the users about the thousands of items showing up in their My Work\Queues\In Progress folder. When I look at my own items, I have about 1000 activities showing in my In Progress folder but when I open them up most of them are owned by someone else. According to the Help description of this folder, only items that I have accepted should show up in my In Progress folder. I've never accepted anything, so I'm not sure anything whatsoever would be showing up in this folder. We used Scribe to import ...

Cell with large amount of data not showing all data
I'm running Excel 97. I have a cell with 358 words (1928 characters with spaces). Word wrap is on for the cell. Only part of the text is displayed even though the cell is big enough to show everything. If I make the cell wider (wider than a page) more of the text shows but not everything. I tried a new worksheet with the same text and had the same problem. Is this a known issue with excel? Is there a solution? Thanks, Brad Left to its own devices, excel will only show about 1000 characters in a cell. But you can add some alt-enters (to force a new line within the cell) and see more s...

Get info from Fidelity
I use money's portfolio manager to retrieve info from my Fidelity accounts. However, it only retrieve stock and bond informations. It did not retrieve the money market fund information. How can I make it download money market fund info? In microsoft.public.money, Peter Tso wrote: >I use money's portfolio manager to retrieve info from my >Fidelity accounts. However, it only retrieve stock and >bond informations. It did not retrieve the money market >fund information. How can I make it download money market >fund info? Fidelity does things a differently th...

Get Xml from web .
This is a link to an address where I would like to access xml. http://setiathome2.ssl.berkeley.edu/fcgi-bin/fcgi?cmd=user_xml&email=XXXX The XXXX is the email address of a user. All I want to do is access the xml and view it in a datagrid. I have read several tuturials and articles and have not been able to find something that would explain how to do this. Does anyone know of a sample code or something close I could use as a reference. I am using visualstudio.net. and visual basic. THX Take a look at using the following classes on your app: HttpWebRequest HttpWebResponse DataSe...

MS Money '04 Reference Books
Is anyone aware of any good MS Money '04 reference books? I've checked the MS Press website, but they seem to have one for every software program other than Money. Any suggestions? Thanks much, Too many books ended up getting pulped--I'm not sure there are any for Money anymore. You might check on http://www.amazon.com. "Mike" <anonymous@discussions.microsoft.com> wrote in message news:e72001c3f056$477b1c10$a501280a@phx.gbl... > Is anyone aware of any good MS Money '04 reference > books? I've checked the MS Press website, but they seem > to...

formula to have 0 to 6.5 only in ref to other cell val of 0<=6.5
...

Moving exchange 5.5 to a new server exchange 2003
Good day all... how do I move exchange 5.5 to a brand new exchange server 2003. The new server will be in a new organization. Any info would be greatly appreciated. Thanks, Ray http://www.microsoft.com/usa/webcasts/ondemand/2340.asp http://support.microsoft.com/default.aspx?scid=fh;en- us;exch2003 Patrick Rouse Microsoft MVP - Terminal Server >-----Original Message----- >Good day all... how do I move exchange 5.5 to a brand new >exchange server 2003. The new server will be in a new >organization. Any info would be greatly appreciated. > >Thanks, >Ray > >. ...

changing a cell of 60 files
Hi all I've 60 files and another one which summarizes all of them.. I've to put a day in the cell a1 and then I'd like to cut and paste that day in the cell a1 of the other 60 files without having to change all of them manually. I'd like to save and then exit every single file.The files are named 0001 0002 0003..and so on. I know that this is possible with a macro..but I've got a problem.. It's possible not to have the prompt which asks for updates of the file everytime I open one of them?? Thanks for the help Rossella Hi Rossella http://www.rondebruin.nl/copy4.htm ...

Outlook 2007 "Move to folder" default setting
By default, in Outlook 2007, when you right-click on a message and select "Move to Folder", the initial folder selected is the last folder that a message has been moved into. Is there a way to have this option always default to Inbox? Thanks much for the help! No, but you can also directly move the message to your Inbox folder via drag & drop instead of using the Move To dialog for this. -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downloads, Add-Ins and more http://www.ms...

Absolute formula moving
I have a query that takes share prices from a web site as date in first column then share prices in following columns across the same row. I then recorded a macro to take that information from that sheet and insert it to the next sheet that contains weekly and monthly data tables. On the monthly and weekly sheet I have made formula’s to figure percentage of change, the tables collect the data in ascending order (recent dates towards the top of the table), for the table formula’s I first tried relative then absolute and finally defined ranges with absolute yet when the data is inserted v...

Move/Resize PlotArea
I have code that creates as separate sheets (not on a worksheet). The charts are included in paper reports and the users need to be able to hold different chart up to the light to see how various cases compare or various results from a similar case. The plots have the same X-Axis configuration (except where it crosses the Y-Axis). However, the Y-Axis parameters are different (scales, number format, etc.). There may be up to 50 charts per case. Therefore, the INSIDE plot area of the charts has to be at exactly the same left, top coordinate and have exactly the same width and height. ...