Using a formula to determine variable rows in Chart

I am trying to build a standard sheet with a chart.  This chart may contain 
28, 29, 30 or 31 rows depending upon the number of days in the month the 
chart is prepared.  A database application loads the data into Sheet1, and 
sheet two contains the graph.  The graph always shows 31 days.  Sheet1 
contains a cell stating the number of days to chart but I am, so far, 
unsuccessful in using a formula or expression when stating the series range 
in the chart data properties.  It seems the only expression the chart 
understands is a static range of cells.  Because of security restrictions on 
desktops I am trying to avoid VBA and would prefer a formulaic solution.
-- 
Thx,
SA Roberts
0
SARoberts (2)
6/28/2007 3:28:02 PM
excel.charting 18370 articles. 0 followers. Follow

4 Replies
295 Views

Similar Articles

[PageSpeed] 29

Hi,

Depends on the version of Excel you are using, let's suppose 2003 - select 
the range where the data will appear on the Sheet1, for example A1:D29 (one 
row for titles and 28 rows for the minimum number of rows needed for Feb.).  
Choose the command Data, List, Create List and click OK.

Now create your chart and select this list range.  When you add more data 
for 30 and 31 day months the chart wifll automatically expand to chart the 
range.

-- 
Cheers,
Shane Devenshire


"SA Roberts" wrote:

> I am trying to build a standard sheet with a chart.  This chart may contain 
> 28, 29, 30 or 31 rows depending upon the number of days in the month the 
> chart is prepared.  A database application loads the data into Sheet1, and 
> sheet two contains the graph.  The graph always shows 31 days.  Sheet1 
> contains a cell stating the number of days to chart but I am, so far, 
> unsuccessful in using a formula or expression when stating the series range 
> in the chart data properties.  It seems the only expression the chart 
> understands is a static range of cells.  Because of security restrictions on 
> desktops I am trying to avoid VBA and would prefer a formulaic solution.
> -- 
> Thx,
> SA Roberts
0
6/28/2007 4:58:00 PM
Shane,
Using 2007 and not sure how I then connect the series in the chart to the 
list.  It seems that 2007 will only allow me to specify 
"=sheetx!cellrange:sheetx!cellrange".  I think your advise appears to be the 
solution I seek, now any advise on a 2007 implementation of your suggestion?
-- 
Thx,
SA Roberts


"ShaneDevenshire" wrote:

> Hi,
> 
> Depends on the version of Excel you are using, let's suppose 2003 - select 
> the range where the data will appear on the Sheet1, for example A1:D29 (one 
> row for titles and 28 rows for the minimum number of rows needed for Feb.).  
> Choose the command Data, List, Create List and click OK.
> 
> Now create your chart and select this list range.  When you add more data 
> for 30 and 31 day months the chart wifll automatically expand to chart the 
> range.
> 
> -- 
> Cheers,
> Shane Devenshire
> 
> 
> "SA Roberts" wrote:
> 
> > I am trying to build a standard sheet with a chart.  This chart may contain 
> > 28, 29, 30 or 31 rows depending upon the number of days in the month the 
> > chart is prepared.  A database application loads the data into Sheet1, and 
> > sheet two contains the graph.  The graph always shows 31 days.  Sheet1 
> > contains a cell stating the number of days to chart but I am, so far, 
> > unsuccessful in using a formula or expression when stating the series range 
> > in the chart data properties.  It seems the only expression the chart 
> > understands is a static range of cells.  Because of security restrictions on 
> > desktops I am trying to avoid VBA and would prefer a formulaic solution.
> > -- 
> > Thx,
> > SA Roberts
0
SARoberts (2)
6/29/2007 4:36:02 PM
You can extend Shane's suggestion and use an Excel 2007 Table to contain the 
data. As the table changes its number of rows, any formulas that refer to 
all rows of the table adjust to include the new length of the table.

Or you could define dynamic ranges that serve as series data:

    http://peltiertech.com/Excel/Charts/Dynamics.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"SA Roberts" <SARoberts@discussions.microsoft.com> wrote in message 
news:A226A7B2-CE8A-45A3-8A34-6C4EAD89B53B@microsoft.com...
>I am trying to build a standard sheet with a chart.  This chart may contain
> 28, 29, 30 or 31 rows depending upon the number of days in the month the
> chart is prepared.  A database application loads the data into Sheet1, and
> sheet two contains the graph.  The graph always shows 31 days.  Sheet1
> contains a cell stating the number of days to chart but I am, so far,
> unsuccessful in using a formula or expression when stating the series 
> range
> in the chart data properties.  It seems the only expression the chart
> understands is a static range of cells.  Because of security restrictions 
> on
> desktops I am trying to avoid VBA and would prefer a formulaic solution.
> -- 
> Thx,
> SA Roberts 


0
jonxlmvpNO (4558)
6/30/2007 1:48:47 PM
Hi Robert,

In 2007 Microsoft has named the command Create Table.  Select one row of 
titles and the sample data as in my earlier example.  Then press Ctrl T the 
shortcut for Create Table (same as Create List in 2003 and the old shortcut 
Ctrl L also works in 2007).  Click OK.  Select the data and create a chart, 
Excel will handle the rest.

This will become the method of choice for both charting and pivot tables as 
time goes by.  -  Prediction by Shane Devenshire
-- 
Cheers,
Shane Devenshire


"SA Roberts" wrote:

> Shane,
> Using 2007 and not sure how I then connect the series in the chart to the 
> list.  It seems that 2007 will only allow me to specify 
> "=sheetx!cellrange:sheetx!cellrange".  I think your advise appears to be the 
> solution I seek, now any advise on a 2007 implementation of your suggestion?
> -- 
> Thx,
> SA Roberts
> 
> 
> "ShaneDevenshire" wrote:
> 
> > Hi,
> > 
> > Depends on the version of Excel you are using, let's suppose 2003 - select 
> > the range where the data will appear on the Sheet1, for example A1:D29 (one 
> > row for titles and 28 rows for the minimum number of rows needed for Feb.).  
> > Choose the command Data, List, Create List and click OK.
> > 
> > Now create your chart and select this list range.  When you add more data 
> > for 30 and 31 day months the chart wifll automatically expand to chart the 
> > range.
> > 
> > -- 
> > Cheers,
> > Shane Devenshire
> > 
> > 
> > "SA Roberts" wrote:
> > 
> > > I am trying to build a standard sheet with a chart.  This chart may contain 
> > > 28, 29, 30 or 31 rows depending upon the number of days in the month the 
> > > chart is prepared.  A database application loads the data into Sheet1, and 
> > > sheet two contains the graph.  The graph always shows 31 days.  Sheet1 
> > > contains a cell stating the number of days to chart but I am, so far, 
> > > unsuccessful in using a formula or expression when stating the series range 
> > > in the chart data properties.  It seems the only expression the chart 
> > > understands is a static range of cells.  Because of security restrictions on 
> > > desktops I am trying to avoid VBA and would prefer a formulaic solution.
> > > -- 
> > > Thx,
> > > SA Roberts
0
6/30/2007 4:14:03 PM
Reply:

Similar Artilces:

Help coping formulas
Excell 2007 I have an IF formula for contents in workbook A. I would like to copy the IF formula into workbook B and have it apply to the contents of workbook B. Suggestions please. Thank you, Bill Does not simply "copy, paste" work ? If I'm not missing something in your question, it should work. Cousin Excel "Bill" wrote: > Excell 2007 > > I have an IF formula for contents in workbook A. I would like to copy the IF > formula into workbook B and have it apply to the contents of workbook B. > > Suggestions please. &g...

How come there are more replies when using a newsreader
I was wondering how come there are more replies in this group when using a newsreader. I now like using a newsreader to view this group but prefer posting in the web based interface. I have noticed that recently there are a lot more replies to topics that are not showing up in the web based interface but are showing up in the newsreader. Does anyone know if this is related to Microsoft starting to close down the newsgroups or is there another reason. Thanks in advance for the replies. It's due to longstanding (i.e., well over a year) synchronization issues (i.e., the sl...

Anyone using GP for a Non-Profit org?
We are a very small Non-profit org (NPO) that needs to modernize so we operate more like a business. I am working on funding a SBS 2003 deployment. Our needs are: a bookkeeping system with various levels of access (our bookkeeper/office mgr, Exec. Dir., staff) that includes grant and donor/membership functionality; grants management; fundraising/donor management; central file and database storage for security, backups, and sharing. We currently use QuickBooks Pro 2003 for NPO's for bookkeeping and have no system in place (essentially) to handle grants, donors, or members. Our ...

Excluding data points in a chart
Is there some way to specify conditions under which data points should be excluded in a chart? In particular, I have two columns for which I want to create a scatter plot. However, some of the rows are missing data for one or the other column. Is there a simple way to tell Excel to ignore such rows? Jim Hi Jim, 1) With the chart selected, use Tools|Options and open the Chart tab; specify what is to happen with missing values OR 2) Where data is missing enter =NA() -- Bernard Liengme www.stfx.ca/people/bliengme remove CAPS in e-mail address "JM" <anonymous@discussions....

Repeat Rows @ Bottom?
Excel 2000 ... Page Setup ... Sheet ... Rows to repeat @ Top ... Can rows be made to repeat @ bottom as well? Thanks ... Kha Ken, Unfortunately, you can't do this. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Ken" <anonymous@discussions.microsoft.com> wrote in message news:054f01c3c883$5fb5d950$a601280a@phx.gbl... > Excel 2000 ... > > Page Setup ... Sheet ... Rows to repeat @ Top ... > > Can rows be made to repeat @ bottom as well? > > Thanks ... Kha > > Thanks Chip ... Such is ...

dates and formulae
Hi Can i put in a formula to subtract one date from another and show the period between in days, weekks or whatever. It is to calculate someones service with the company. It is many different periods spanning 30 years. thanks Gary Take a look at Chip Pearson's explanation of =datedif() at: http://www.cpearson.com/excel/datedif.htm geepeetee wrote: > > Hi > > Can i put in a formula to subtract one date from another and show the period > between in days, weekks or whatever. It is to calculate someones service > with the company. It is many different periods spa...

Determine Last Login time
Is there any thing that can be used to unquestionably determine the last time a user logged in to their mailbox? Have ex'03 sp2 on win '03 sp1 On Tue, 17 Oct 2006 07:31:03 -0700, Maive72 <Maive72@discussions.microsoft.com> wrote: >Is there any thing that can be used to unquestionably determine the last time >a user logged in to their mailbox? > >Have ex'03 sp2 on win '03 sp1 Mailbox logons can be a challenge, it being far easier to check for logged on accounts: Active Directory Users & Computers and then right click the domain and select Find, Co...

Error 553 using Outlook 2007 and Vista
Hello. I'm getting the following error using Outlook 2007 and Vista: 553 sorry, that domain isn't in my list of allowed rcpthosts (#5.5.3 - chkuser) When I test the account settings, it's fine. I can recieve just fine, and the only outgoing messages without this error are for recepients with my same domain. I've turned off my antivirus and windows firewall. Still this doesn't work. Any help would be greatly appreciated. 550 errors usually mean you're not authenticating properly, or you're attempting to use an SMTP server other than the one your ISP provides. ...

Need to select rows to average based on a value in a different column
Below is an example of my spreadsheet. I'd like to find the average number of users for each "Day of the week" hour combination but I'm not even sure where to start. Date Hour DoW # Users 10/27/2003 10 Mon 11 10/27/2003 11 Mon 11 10/27/2003 13 Mon 10 10/27/2003 14 Mon 11 10/27/2003 15 Mon 9 10/27/2003 16 Mon 9 10/28/2003 8 Tue 5 10/28/2003 9 Tue 6 10/28/2003 10 Tue 4 10/28/2003 11 Tue 8 10/28/2003 13 Tue 7 10/28/2003 14 Tue 7 10/28/2003 15 Tue 7 10/28/2003 16 Tue 10 10/29/2003 8 Wed 7 10/29/2003 9 Wed 10 10/29/2003 10 Wed 11 10/...

Copy to Clipboard using Dexterity
Hi, I want to override the Line_Pre event to copy the Item No to the clipboard. Is it possible to copy a text field' value to clipboard in Dexterity? Thanks. MH. Why would you want to do this? If you are attempting to 'save' the item number value somewhere before you override with your script won't it make sense to store it to a global variable or a local variable? Working with the clipboard is simply not practical. Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com The Dynamics GP B...

Create Graphs using VBA
Hello, I trying to implement a routine to create Powerpoint slides using a function from Microsoft Knowledge base 200551. There are 2 prolblems I am having with this that I'm hoping someone here can help. I have included the code from this site below. The first one is the routine uses early binding to the the Powerpoint library so the intellisense doesn't work. I am going to need to make some modifications to this such as passing labels, changing chart types etc. and it would relly help if the intellisense was working. My efforts to change this have not been succe...

Dsiplay pivottable value item in row heading
How do i display the item at the value field display row heading instant of display in column heading?Example: Date Number | 11/20/2007 11/21/2007 ..... 1 | Color Green White ..... 2 | Color Purple Brown ..... 3 | Color Yellow Black ..... Original will be display like this: Date 11/20/2007 11/21/2007 ..... Number | Color Color 1 | Green White ..... 2 | Purple Brown ..... 3 | Yellow Black ..... Idea?Thanks in advance. ...

When to use CCriticalSection?
I have an app in which there are 4 threads running simultaneously. Right now am using "Events" to do the synchronization between threads. Like, there is a Server writing thread and then there is a Server reading thread. Once Read thread, reads data from Server, it will Set an Event for Write thread to continue. So my question is, In this scenario do i need to use CCriticalSection? >I have an app in which there are 4 threads running simultaneously. >Right now am using "Events" to do the synchronization between >threads. > >Like, there is a Server writing ...

Excel using =RC[-1] format and I don't want it
On this machine only, Excel will use the format =RC[-1] when I am entering a formula on the command line by hand and click on a cell to reference it in the formula. I don't know how this got on but I would like it to go off. On my other machines, it does the old familiar A1:B2 style of cell reference. I can't even search for how to fix it since I can't determine what this is called so I can turn it off. The only reference I have seen is a button that is only effective for macro creating and I am not creating a macro, just a formula, interactively, in the cell. How ...

Master image to use in separate sheets?
Hello All, Does anyone know if there is a way to tell Excel to use the same image/background for each sheet, instead of needing to import for each one? Or is there a way to set up a (similiar to Power Point/Master slide). Thanks in advance, drew If it's just for one workbook, I'd have a master worksheet in that workbook that I could just copy. If it's for lots of workbooks, I'd set up a workbook with one sheet that had that background the way I wanted it. Then save this workbook as a Template (let the folder default to excel's favorite). Tnen when you rightclick on...

date at chart
I am using MS EXCEL 2003. I want to add date dynamicly (only MONTH)to a chart. How can i do this? -- external ------------------------------------------------------------------------ external's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=36592 View this thread: http://www.excelforum.com/showthread.php?threadid=563748 In article <external.2bawah_1153494309.1666@excelforum-nospam.com>, external.2bawah_1153494309.1666@excelforum-nospam.com says... > > I am using MS EXCEL 2003. > I want to add date dynamicly (only MONTH)to a chart. > How ca...

Use query for row source
Similar to JHC message and I tried fredg recommendations with no luck. Running Office 2003 Professional Edition 2003 Converted database from 2000 to 2003 (problem existed in 2000 as well) Created Combo Box w/control wizard I can create the Combo Box usin the wizard, select required fields (only need one but tested to see if it happens w/all fields which it doesn't. I can see data for column one, primary key) and while still in the wizard, the combo box is displaying all information I'm looking for. Once I finalize wizard, switch to form view, the only data displayed in the comb...

Where do I ask about formulas?
I posted a few questions in the excel.worksheetfunctions group. Was that a bad idea? It doesn't seem very active. What would this be a better group to post a question about formulas? Thanks. The name was changed to excel.worksheet.functions "Bruce Norris" <bruce25norris@yahoo.com> wrote in message news:LXi4e.28034$f%4.25742@bignews1.bellsouth.net... > I posted a few questions in the excel.worksheetfunctions group. Was that a > bad idea? It doesn't seem very active. > > What would this be a better group to post a question about formulas? > > Tha...

data source for chart
I have spreadsheets with a lot of data in them. I have multiple columns. The two columns I need to compare right now have initials in one column and dates in another column. The initials are nurses and the date column is a denial date. I need to create a chart that will show a possible trend of certain nursing staff having a higher number of denials. I know I highlight those two columns, but I think I have to change the date data to something that can be counted??? Please advise. I am desperate. and there are more of these kind of charts to come that I will have to do. Thank yo...

formulas #32
I need to add up cities and i cannot for the life of me figure out how to get the cities added in a formula. PLEASE HELP ME . thank you Hi, Not sure what you mean with add cities, you want in one cell two cities from other two columns, if yes use =A1&A2 if you need an space =A1&" " &A2 "leeammatt" wrote: > I need to add up cities and i cannot for the life of me figure out how to get > the cities added in a formula. PLEASE HELP ME . thank you ...

Variant Array Copy, Array row contains Object
Hi All, For the first time I had a need to put an object inside a two dimension variant array. The proc below was for some initial testing. I had to code an object test at the line marked with 1. I was surprised that the 'copy' at line 2. worked. Can you tell me why? Thanks. Neal Z. Sub Test() Dim vArray, vArrayB Dim Row As Long, Col As Long ReDim vArray(1 To 2, 1 To 2) Set vArray(1, 1) = ActiveWorkbook vArray(1, 2) = "abc" vArray(2, 1) = 24 Set vArray(2, 2) = ActiveSheet ReDim vArr...

How can you identify folders, using folder numbers?
Hi everybody, I've searched and searched and asked in the quasi-defunct yahoo exchange newsgroup, but I've never found an answer as to how I can actually utilize the folder identification information contained in Exchange error log events. An example is as such: Event: 1173 Source: MSExchangeIS Mailbox Store Description: Error 0x6bb deleting unused restricted view from folder 1-1A42FF on database "First Storage Group\SG1 Mailbox1". It would be insanely useful if I could tell which folder 1-1A42FF is, so I can narrow down where the problem is occurring, you know?? Anyone...

Using on-the-fly in a report?
Hi I have 2 txt boxes in my form that do calcualtions using the control source. For example the txt box margin has a control source of netcost-jobcost (which are also txt boxes). As the two txt boxes results are not stored in a table can someone please tell me how I can get the results to show in any report that has "margin" as a field. What control do I use for it to pick up the results from the calculation that was made in the form. Not every report has both netcost and jobcost in. Should I put them both in there and make them invisible and then do the calculation again in the con...

Missing sections in CRM 3.0 using XP
I have upgraded my computer to XP PRO Sp2 and now I'm missing some sections, namely Setting on the left side of the screen. The Setting menu is in the GOTO menu but it doesn't work. I get a blank screen. Also the Articles function is missing in the NEW Menu. I can go to another computer running WIN2KPro and it works fine. I'm pretty sure it's just a setting in the Internet Options of IE but I have compared and don't notice a difference. Yes, I have turned off the local firewall. Ernest ------=_NextPart_0001_7245F7CE Content-Type: text/plain Content-Transfer-Encod...

Counting Rows/Columns for Copying Formulas
I have two spreadsheets in a workbook as follows: Source Worksheet contains by customer each product it purchases and the sales for each product (see sample below). Not all customers buy each product. There are also new customers added/deleted each month and new products added/deleted each month. Column A Column B Column C January February..... Customer A Product Code 1 Customer A Product Code 1 100 Customer A Product Code 2 200 Customer A Product Code 3 300 Customer B Product Code 1 500 Customer B ...