Source Data Ranges in Charts

Is there a way an Excel chart (Excel 2000) can dynamically determine th
beginning and end of source data ranges?  Perhaps based on markers i
the worksheet, or the use of a function of some sort in the data sourc
panel.

Imagine a single column of values from rows 1 to 2000.  This column o
values describes a series of "events" in order from top to bottom o
the column.  Each event equates to a separate data series in the chart
In the chart each data series is hardcoded with the beginning an
ending cell references of each event.  

The problem is this:
When the parameters of the underlying values changes, not only do th
values being plotted change (no problem since they are updated in th
chart automatically), but the beginning and end of each data serie
also changes.  So far, the only way to update the ranges of the sourc
data seems to be to right click on the chart, go into data source an
manually type in the new beginning and ending cell references.

This itself would not be a problem except that we are talking abou
many, many worksheets and more than a hundred charts that requir
editing whenever a change takes place.  It would be a big help if ther
were a way that Excel could determine where the beginning and end o
the series are by (in concept) moving markers on the worksheet tha
indicate the beginning and end of each series.  I have not had any luc
so far.  

I appreciate any ideas.  Hopefully the explanation is fairly clear.  
can provide any clarification that may be needed.  Thank you for you
time.

Alan Gange

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

0
xp1rz (1)
11/30/2003 6:25:36 AM
excel.charting 18370 articles. 0 followers. Follow

1 Replies
482 Views

Similar Articles

[PageSpeed] 6

If the data set for each event is contiguous (i.e., no gaps in between 
the start and end cells), you could use the concept in the Excel | 
Tutorials | 'Dynamic Charts' page of my web site.  Yes, you would have 
to change all the charts so that they use the appropriate named 
formulas, but after that, they would update automatically.

-- 
[Posted directly to the Usenet newsgroup -- no affiliation with 
Excelforum]

Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <A.G..xp1rz@excelforum-nospam.com>, A.G..xp1rz@excelforum-
nospam.com says...
> 
> Is there a way an Excel chart (Excel 2000) can dynamically determine the
> beginning and end of source data ranges?  Perhaps based on markers in
> the worksheet, or the use of a function of some sort in the data source
> panel.
> 
> Imagine a single column of values from rows 1 to 2000.  This column of
> values describes a series of "events" in order from top to bottom of
> the column.  Each event equates to a separate data series in the chart.
> In the chart each data series is hardcoded with the beginning and
> ending cell references of each event.  
> 
> The problem is this:
> When the parameters of the underlying values changes, not only do the
> values being plotted change (no problem since they are updated in the
> chart automatically), but the beginning and end of each data series
> also changes.  So far, the only way to update the ranges of the source
> data seems to be to right click on the chart, go into data source and
> manually type in the new beginning and ending cell references.
> 
> This itself would not be a problem except that we are talking about
> many, many worksheets and more than a hundred charts that require
> editing whenever a change takes place.  It would be a big help if there
> were a way that Excel could determine where the beginning and end of
> the series are by (in concept) moving markers on the worksheet that
> indicate the beginning and end of each series.  I have not had any luck
> so far.  
> 
> I appreciate any ideas.  Hopefully the explanation is fairly clear.  I
> can provide any clarification that may be needed.  Thank you for your
> time.
> 
> Alan Ganger
> 
> 
> ------------------------------------------------
> ~~ Message posted from http://www.ExcelTip.com/
> ~~View and post usenet messages directly from http://www.ExcelForum.com/
> 
> 
0
11/30/2003 3:29:09 PM
Reply:

Similar Artilces:

How do I name categories of a pie chart
I am creating a pie chart of numeric values. I would like to add the names of each category. I don't seem to be able to find out exactly how to do it. HELP!! Right click within the chart, select "Format Data Series..." Go to "Data Labels" tab. Select "Series Name" or "Category Name" "RGC" wrote: > I am creating a pie chart of numeric values. I would like to add the names > of each category. I don't seem to be able to find out exactly how to do it. > HELP!! ...

Help with using data
Hi I would really appreciate some help with extracting data from Outlook. I have been building up a database of clients/prospectives, these are in various categories. I would like to extract/search on various fields as well as my own categories, all i can do with export is extract the whole lot to a tab delimited file. Or export categories to an rge file which I can't use. Any ideas out there? thanks Outlook's Search doesn't suffice? There are 3rd party search applications that might help. What sprcifically stops you from exporting to a format other than TSV? There are other...

WriteXml, DateTime, and Timezone data
This is a multi-part message in MIME format. ------=_NextPart_000_0040_01C5D4C2.CF4AB1D0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Greetings. I am using a myDataSet.WriteXml(StringWriter) function (and then using = the ToString() function to convert it to a string) to build an XML = string based on the contents of my dataset. This in itself works great. I am then passing this string back to a PowerBuilder module that is = taking this XML string and doing what it needs to do with it. This is = almost working great as well. It wo...

entering new data in a saved spreadsheet without losing formulas?
How do I do this? Hi Jackie, As long as you do not overwrite the formulas, then any new data will not affect the formula. I suspect though that there is more to this question. Are you concerned about writing new data into a cell that has a formula in it? The way to stop a cell's data being overwritten is to put protection onto it. This means taking protection off all the other cells otherwise you will not be able to write data to any cell. First select the whole worksheet concerned, then go to Format\Cells and select the protection tab and unselect the 'locked' tick box,...

Data Connection to TXT File
I want to create a spreadsheet with multiple worksheets. For each worksheet, I want to indicate it's source of data is an external text file. Every time I open the spreadsheet, I want the data from each text file to be reloaded into the spreadsheet (the number of rows in the text file may change so the number of rows populated with data in the worksheet will change). I'm using 2007 and have been playing with data connections but I'm clearly missing something because nothing is happening. Help! Can someone tell me if I can even do this and if so, give me step by step inst...

How do I make a graphic with non-numerical data on X axis ?
I would like to make a graphic with non-numerical data on X axis (e.g. names of persons or cities). When I use the normal graphic construction procedure in Excell, numbers are generated (1, 2, 3 ...) in place of the text of the X axis column. can anyone help me? Thanks. Select the chart, and choose Source Data from the Chart menu. Click on the Series tab. Make sure the appropriate range is indicated in the Category (X) Axis Labels box. This cannot be done in an XY chart, but will work for Line, Column, and other chart types. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and...

Data format for pages
I want to change the format control properties "DataFormatAs" to a currency in Data Access Page (access 2000). Its asking for a code but I dont how?. Its easier in newer versions Select the Data Access Page > choose design > double click the field/cell whose format you want changed > Hit Design tab > Format > Change to Currency. -- HTP Adnan "Newguy" wrote: > I want to change the format control properties "DataFormatAs" to a currency > in Data Access Page (access 2000). Its asking for a code but I dont how?. Its > easier in newer ...

Clean all "data" from a database
Access 2007 on Win XP I have an existing database that I want to use for a new function. I do want to start out with empty tables after the demonstration and training phase. Therefore I am looking for a way to empty all data records from all tables. I have read and understand the process of creating a new database and exporting the tables as structure or definition only. My question is in Access 2007 is it possible to execute this procedure on more than one table, query,form, or report at a time? I have 10 tables, 20 queries, 22 forms, and 32 reports. I have attemptted to se...

Chart with merged rows
Another question for EXCEL gurus ! I have a chart with 56 rows (corresponding to 56 weeks in a year), each of which is composed of merging 7 rows (corresponding to 7 days/week). How can I get a graph with the values in these merged columns ? Selecting these and clicking the chart icon does not produce anything ! X-axis C, Y-axis - D -------------------------------------------------------------------------------- A B C D --------------------------------------------------------------------------------- 13. Aug. Sunday ...

How do you point to a named range in linked workbooks?
Workbook #1 has a range named "Gross Sales" in Sheet1 Cells A2:K2. How can I point to this range so that I can link it to Sheet1 Cells2:K2 in Workbook #2? Is this what you're looking for...? In workbook 2 enter "=[Book1]Sheet1!$A$2" in cell A2. Do the same for cells B2 through K2 with the correct respective links. Hope this helps. -Chad "KG" wrote: > Workbook #1 has a range named "Gross Sales" in Sheet1 Cells A2:K2. How can I > point to this range so that I can link it to Sheet1 Cells2:K2 in Workbook #2? Or, if you don't like to ...

Scatter Chart Question #2
Hi Folks: I've made a scatter chart that looks the way I need it to. When I "hover" the mouse over the data points, I get the point values as I expect in the yellow box. My question: Instead of the data point values, is it possible to get a picture in the pop-up ?? Many thanks Steve You don't have a lot of control over the content of these chart tips, beside turning them on and off. If you want to customize the labels, you can download the Chart Hover Label add-in from Tushar Mehta's web site (http://tushar-mehta.com). To show a picture you will have to r...

Problem importing data from text file
Hi, I have a comma delimited file that has twenty column names followed by six rows of data. I am trying to import this into Excel. When I do this, the data imports entirely into row one. It does import into difference cells but there is one row with 120 cells of data. I was expecting it two "Wrap" and start importing the data into cell A2. Can anyone help with this? How do I show the break between column headers and data? Thanks, Chuck ...

How do I transfer email data from WinXp to Windows 7
I just set up a new computer with Windows 7 and need to transfer my email folders and emails contained therein from my old computer which has outlook express in WinXP. I would prefer to transfer the data with a USB flash drive as I have no cables or other perhipherals to utilize another method. I've checked the internet and tried several methods suggested therein but nothing has worked. Moreover, theres no reference with regard to transferring from Outlook Express to Windows Live Mail (Windows 7), if that has any bearing on my ability to effect the transfer. This may not b...

Chart item reports with OUTLOOK via Excel or ?
I want to create reports for OUTLOOK items (particularly tasks). I'd like to be able to see charts that display things like the amount of time spent on tasks for various clients, the type of work done, and the dates (including time of day). I'd like to be able to look at these reports daily if possible. Is there a way that I can do this? I thought that there must be a way by exporting the items into excel then manually setting up charts but this would be time consuming to do each time. Any help is appreciated (I've spent days trying to figure this out) ...

Data Validation
What I am trying to achieve is a drop down list that would look something like this: D1a - suipplies F1g - cell phones C2a - travel E4b - computers and so on and so forth. I can do that. What I would like to happen though is in the same cell that the drop down box is in (a29) I would like to select one of those options and only have D1a, or F1f, ect show up in the cell. The words are just references to my budget, and I don't nned them after I selected the right one. Is this possible? I am somewhat new to excel and have never went into the visual basic editor and I don't have...

Axes(xlCategory) for a chart-sheet in 2007
I am unsuccessfully trying to set TickLabelSpacing for X-axis in a chart-sheet. Tries recording the steps into a macro, but the recorded code returns an error when i attempt to run it myself. The problem has something to do with the fact that the charts in chart-sheets aren't embedded into ChartObjects, and setting TickLabelSpacing directly for a chart object doesn't do anyhting. Here's the recorded code (fails in 2007): Sub Macro3() ' ' Macro3 Macro ' ' Sheets("Chart").Select ActiveSheet.ChartObjects("Chart 1").Act...

excel data base modification
hi there. i need some help please guys. i have exported some names & telephone numbers from a software packag (sms centre) & im trying 2import them in2 a package called e-txt. thes packages reads csv files. ive encountered a problem my exported files r saved in excel lik this... name - country code - area code - phon number joe bloggs - 44 - 07977 - 797898 but the e-txt database fields r as follows 1st name - surname - phone number joe - bloggs - 447977797898 ...

Importing data into lead with custom attribute
I have created a custom attribute (gg_category) for the lead entity. The attribute exists on the default lead form. The customizations have been published and IISRESET has been issued. When I try and import data from a "clean" CSV file, I get the following error: 'Lead' entity doesn't contain attribute with Name = 'gg_category'. What am I missing here? Is there an extra step that I have left out? Thanks. Or ...

Copy and paste two named ranges together.
I am attempting to copy and combine two named ranges of equal size into a blank spreadsheet. How does one copy the first named range and concurrently seperate each copied row with a blank row into the blank spreadsheet, and copy the second range and paste those copied records into the blank rows? I am looking for a systematic way of doing this consolidation. There can be hundreds of rows of data. Also the named ranges can very in size month to month. The end result is to combine two ranges for a journal entry upload into a financial accounting entry. The two arrays represent the ...

Merging Data into Word and Want Checkboxes (Yes/No data type)
I am trying to merge data from my access database into word. I have several items that are checkboxes in access. How do i get those checkboxes to merge into word so that i see them. Right now they just produce numbers if they are checked or not. Thanks I'm not sure you can do with with a straight mail merge. The following is a snippet of code from one of my applications. The app relies on documents that have already been bookmarked with simple bookmarks (for fields longer than 255 characters), checkboxes (for y/n fields), and textboxes for everything else. The merging is d...

MS Query Data Source Change
I have a series of saved queries that I use on an ODBC data source. I would like to put them on other computers that may not have the same data sources but have different ones (tables are the same in each data source). Right now I am editing the query in Notepad to change the data source and path to the correct one on the other computers but am wondering why it is not evident how to change this in the query itself. Can it be done? How? TIA I had a similiar problem which I solved with a form/macro. (I don't think you can point to a different source other than manually changing t...

How do I use the data in a named field to select a worksheet tab .
I need to use a named field to select a worksheet tab in another workbook. ie i have a workbook called Master and need to collect data from a workbook called Reports, but i need to collect it from different worksheets within Reports depending on the month selected in Master. The tabs in Reports are M01,M02,etc. In Master I have a Named Field called 'Month" with M01,M02 etc entered in it. This field needs to select the corresponding tab in Reports. Usually the reference syntax goes something like '[Filename]Tabname!Cellname or reference. Here is an obscure example from an old...

Getting External Data
Using Microsoft query I manually edit the SQL to the following. select gl30000.ormstrid as customer, sum(gl30000.crdtamnt) as credt, sum(gl30000.debitamt) as debt, sum(gl30000.crdtamnt) - sum(debitamt) as diff from gl30000 join gl00100 on gl00100.actindx = gl30000.actindx and ((gl00100.actnumbr_3 >= 2500 and gl00100.actnumbr_3 <= 2599) or (gl00100.actnumbr_3 >=1400 and gl00100.actnumbr_3 <= 1499)) where gl30000.trxdate < '2003-07-01' group by gl30000.ormstrid order by gl30000.ormstrid The query runs successfully and I the data goes into Excel....

chart type with multiple x axis
I found a sales dashboard that I like. But I can't find a way to do this in Excel. Has anyone seen the ability to make the horizontal bar charts like the example here? http://centerview.corda.com/corda/dashboards/salesdb/main.dashxml On Thu, 30 Aug 2007, in microsoft.public.excel.charting, MJ <MJ@discussions.microsoft.com> said: >I found a sales dashboard that I like. But I can't find a way to do this in >Excel. Has anyone seen the ability to make the horizontal bar charts like >the example here? >http://centerview.corda.com/corda/dashboards/salesdb/main.da...

Data Migration Question 11-05-04
Hello, when migrating contacts -and leave values for the "Prefered Contact Methods" (like Phone->True/False, Email-->True/False, etc.) in cdf_contact blank- does CRM populate them in ContactBase with default-values set in customization? I checked this on table contactbase and the values were not set to default. They have the value 'null' -Is this correct? Just thouht the DMF uses the SOAP Interface itself for generating Crm entities. Can you confirm if this behavior is correct? Cheers; DS The DMF does use the SOAP interfaces. However, those interfaces are configur...