How do I pass series data x-value range to a chart from a cell?

I have a series on a line chart (x-values "=Sheet1!$A$1:$A$10"). I also have 
two worksheet cells (B1 & B2) that contain the start and end date for the 
x-axis of the chart. I want to be able to link the chart series data to the 
start and end date values so that the chart automatically adjusts when I 
change the dates in the worksheet cells. I can create a string or address for 
the x-values but how do I do not know how to pass this to the chart series??

I know I can do this using a macro, but I want to do it without macros so 
that friends who use MicrosoftCharts (the cheap, no macro version of Excel) 
can still use the charts.
0
ian (162)
3/3/2006 2:21:29 PM
excel.charting 18370 articles. 0 followers. Follow

1 Replies
641 Views

Similar Articles

[PageSpeed] 55

See
Dynamic Charts
http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/index.html
particularly, example 3.

Since I don't know anything about MicrosoftCharts I cannot say if named 
formulas will work there.
-- 
Regards,

Tushar Mehta
www.tushar-mehta.com
Custom business solutions leveraging a multi-disciplinary approach


"Ian" wrote:

> I have a series on a line chart (x-values "=Sheet1!$A$1:$A$10"). I also have 
> two worksheet cells (B1 & B2) that contain the start and end date for the 
> x-axis of the chart. I want to be able to link the chart series data to the 
> start and end date values so that the chart automatically adjusts when I 
> change the dates in the worksheet cells. I can create a string or address for 
> the x-values but how do I do not know how to pass this to the chart series??
> 
> I know I can do this using a macro, but I want to do it without macros so 
> that friends who use MicrosoftCharts (the cheap, no macro version of Excel) 
> can still use the charts.
0
3/3/2006 4:10:29 PM
Reply:

Similar Artilces:

cell
Are there any way to add text and a function in the same cell? For example Hello =sum(XX:XX) Try something like ="Hello "&SUM(A1:A10) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com chip@cpearson.com "danne" <modig123@hotmail.com> wrote in message news:0a7f01c36335$498056f0$a401280a@phx.gbl... > Are there any way to add text and a function in the same > cell? For example Hello =sum(XX:XX) Sure, for example: ="Hello = "&SUM(A1:B1) For more info, try MVP Debra Dalgleish's &...

Macro or Formula needed to search data in cells
Hi I am looking for a way to create a formula/macro to do the following: My worksheet setup: A1:A30000 C1:C5000 01 02 03 04 05 06 07 08 09 10 01 04 05 06 08 09 12 22 23 27 02 03 04 05 06 07 08 09 10 11 01 03 05 06 07 08 09 14 22 32 03 04 05 06 07 08 09 10 11 12 04 05 06 07 08 09 10 11 12 13 etc Each cell contains a 10 number sequence. The range A1:A30000 is my randomly generated sequence. And C1:5000 is my database of archived sequences. If I wish to check if the combination in cell A1 is anywhere in the range C1:5000. I use the formula. =IF(COU...

Dynamic Bar of Pie Chart: Series1 Label & Value Issue
I have created a Bar of Pie chart and everything works great. I even have named ranges on my data so that the bar portion automatically updates when the data changes. However the Series1 Label on the pie itself does not change to reflect the new total so that it is the sum of all the values in the right column below. Does that make sense? I have one series and my data looks like this: Contracts 100 Name1 100 Name2 150 Name3 200 Name4 75 Name5 50 Name6 25 The pie portion has Contracts & Other as labels with the totals of...

negative values on axes
hi there; this is my first mail here. well my x axes start from -2 and my Y axes data start from -5, I change both to -5 (scale) they are shown (XY scatter dots) as cross, like + sign. what I need: how to change the graph punt the joint axes point from 0 to -5? so the chart shws as usual not like a cross. Best Darius Hi, Change the Values axis crosses at to -5 for both axis. This is an option on the Scale tab of the Format Axis dialog. Cheers Andy Darius wrote: > hi there; > this is my first mail here. well my x axes start from -2 and my Y axes data > start from -5, I chang...

Search for data in a column bring all related items in other colum
I have data in the excel sheet with .A column for OrderNo and B Column for items for that Order. A B ORD001 ITEM1 ITEM2 ITEM3 ORD002 ITEM4 ITEM1 ITEM5 There are more than thousand orders.I want search for an Order so that it brings all items with it. I cannot use filter in A column as it doesn't recognise blank cell.So Option to repeat order nos in A for every item involves a lot of data entry. Any Solution ? I'd fill those empty cells in column a with the previous value. And us...

how do I get more than 19 bars on a bar chart
Hi! I have three columns of data each containing 84 entries. These are grouped in threes, and I wish to create a bar chart to show the comparison between groups A, B and C. I use the chart wizard, create a bar chart, then create the series. Then for each series I go down the column with CTRL held down, selecting each third entry. This is fine for 19 entries, but when I click to select entry no. 20, the series clears itself. If I highlight 20 entries and then click on Insert/chart, I get the message 'Series formula is too long'. Can anyone explain please how to over come t...

How to export values from XY (Scatter) graf?
Is it possible to export each x and y value from XY (Scatter) graph? ...

Compare cells, update based on Ifs
I need to compare cells to the cell above them and to the right of them. Based on the comparisons I'll need to update the original cell with one of those adjoining cell values. After I finish with one column then I need to repeat the procedure on the column to the LEFT of the original column. I know IF, THEN and ELSE statesments but I don't know VB for Microsoft Office products. Range could be all 65,000+ rows on a workseet Start on ColumnJ, Row2 If ColumnJ, Row2 is Null _ If ColumnK, Row 2 is Not Null _ If ColumnJ, Row 1 is Not Null _ ColumnJ, Row2 Value is ...

If cell value is greater than another cell value, clear contents.
Okay, hopefully this is the last question today! I've found the below formula in one of the posts here, but my brain has completely failed on me and I'm not sure how to adapt it to my requirements. I have a value which is entered by the user after a prompt, that populates in cell G4 in a sheet called 'Lookup'. For arguments sake, the current value is 30/11/09. I have another worksheet, 'Data', which has a column populated with dates (say column B). I'd like a macro that looks in column B on the data sheet, and clears out any dates which are gr...

Why does linked data from blank cells become a zero?
When linking data within the same workbook (to different tabs) any cell that is empty - shows up in the new tab as a ZERO (0).....what am I doing wrong? I want those cells to remain blank. That's just the way it is .. Instead of : =Sheet2!A1 you could use an error trap: =IF(Sheet2!A1="","",Sheet2!A1) Alternatively, we could suppress the display of extraneous zeros in the entire sheet via: Click Tools > Options > View tab > Uncheck "Zero values" > OK -- Rgds Max xl 97 --- GMT+8, 1� 22' N 103� 45' E xdemechanik <at>yahoo<dot&g...

Date chart formula question?!?1
Hi all, In a very old spread sheet i was using this formula =IF(OR(AND($D7<G$6,$E7>F$6),AND(F$6>=$D7,F$6<=$E7)),".","") and it worked for dates pre 2009. However, i now wish to use it for dates between 2010 - 2011. Each column represent a week and the rows will indicate and project or task. can anyone help? There is nothing it that formula that will make it work for pre-2009 and not for later years. Perhaps you need to look at your data values? If you have a problem, you could tell us what values are being fed into the formula when it works...

How change dimensions of data label text box in pie chart?
In Excel 2003, I can't seem to re-shape the data labels text boxes in my pie chart. I can change the font and allignment of the text within the text box. But, if the text defaults onto two or three lines, I cannot stretch the text box out to get the text to stay on one line. Normally, you can pull on the the little handles on the text box. Is there a trick to this that I don't know about? This is a deficiency in Excel's charting capabilities. The text elements seem like regular text boxes, but you are unable to resize or reshape them. Even VBA can't do this. The workar...

calculated values in a key field
HelloIs it possible at all (presumably in a form) to create the value to be stored in a key field based on another field (or two).For example, to have the form insertmitchp for a Customer ID based ona surname of mitchell and a first name of peter.I know the calculation - done many many times in a query - and also VB - but I want the system to actually store the calculated value mitchp.(Yes most of the real world may use numbers and autonumbers - but nevertheless ...)Any suggestions welcome.Peter Realistically, you should never store calculated values. You're trying to store 2 pieces of...

How do you ensure that null-valued elements are not serialized?
I am using complex types in order to support serialization/deserialization of floating point numbers, since floating points can't be null. I've seen how to suppress attributes that are "not specified", such as having a float member called Value, and a bool member called ValueSpecified. This instructs the XML Serializer to omit that attribute altogether if it wasn't "Specified". But how can I tell it to omit the XML element altogether? Here's the problem: I deserialize an object that looks like this: <person> <name>Joe<...

Getting data point from an accel chart
I have an excel chart that I want to get the data points(x,y) from. The source data refers to cells in the same sheet, but I don't see any cell in that work sheet. Only the chart. I can run the cursor over the line and get the x,y coordinates for each point. I would like to copy the x,y values, to reproduce the curve in another program ...

Multiple Cell Formats
Is it possible for a column to have more than one format, but for those formats to be similar. Eg. I would like to have a multi-format date column. The format can either be Month-Year (Jan-99) or Year only (1999). (I would even like to have something like Jan/Feb-99, but I can live without that.) Any help would be appreciated. Thanks. Hi With true Excel dates in column A e.g. 01 Feb 07, in column B =A1 Format>Cells>Number>Custom> mmm-yy will give Feb-07 or =TEXT(A1,"mmm-yy") =A1 Format>Cells>Number>Custom> yyyy will give 2007 or =TEXT(A1,"yy...

Area, perimeter, and color in shape data
Hi everyone, I have Visio Professional 2007. First, I don't have great skill yet in making shape data. But also, I would like to have the following data show in the shape data for various geometrical shapes (like squares, rectangle, or circles, for example): - the area of the shape - the perimeter of the shape - the color of the shape Now, I understand that I can go to Tools > Add-Ons > Visio Extras > Shape Area and Perimeter to get the first two items. I also know that this information will change dynamically as I alter the shape. So far, so good. ...

Filtering Excel 2003 Charts to a date range
I have multiple charts that we look for trends on over 1-2 year time frames. Is it possible to have a chart show all the data that has been entered and when looking at the chart have a date range data to format that range differently. What I am trying to do is we have weekly or montly meetings, in these meetings I would like to show all the additional data differently that has been added since our last meeting, while showing accumulative data in a different format. The easiest way to get different formats is to use different series. If you had one series, you could use autofiltering to...

Trying to automatically import specific data from Excel into Word:
Thanks in advance for anyone who can help me with this: I'll start by explaining the spreadsheet I created in Excel (an inventory / information worksheet): It is pretty simple; approximately 12 columns. The key column is the item number / UPC / bar code number, then after that there are various information columns including item name, description, condition, picture links, etc. Secondly, I created a template Microsoft Word document that is relatively long, but will have a few lines (give or take...about 7) customized for each item on the inventory. What I am trying to d...

ADDITIONAL INFO RE: Data execution prevention
Since my last post, I have some additional info that might help someone get an insight as to what the problem may actually be. First, I went to start/run and put in "sfc /scannow", and let it run fully. Everything copacetic there. The I checked Admin. tools for the read-out. Under the "Error" heading was this info: Faulting Application rundll32.exe version 5.1.2600.5512, faulting module unknown, version 0.0.0.0. fault address0x00000000 Clicking on the "Online Help" link brought up a blank page - no help. Hoping some terrifically insightful MV...

Data was deleted and saved by mistake..need to go back
I need to go back to the data before it was saved, can anyone help Richard don't get your hopes up. Unless you have a copy or a backup ... it's gone. Regards Trevor "Richard" <anonymous@discussions.microsoft.com> wrote in message news:1084801c3bea6$222d5270$a601280a@phx.gbl... > I need to go back to the data before it was saved, can > anyone help ...

Hide one month in a chart
I have data from July 2006 to January 2007 that I need to chart. The only catch is that there is no data for Dec 06 (we do not capture this data due to the Xmas rush). On the chart I would like to remove Dec 06 but have not succeeded. Any ideas? In the cell where the Dec 6 data should go type =NA() This will display as #N/A Excel will ignore it when it charts But Dec 6 will still be on the x-axis (it would be misleading to not have it) best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email <dkbleechmore@optusnet.com.au> wrote in message news:1170456927...

Perserving secondary axis in pivot chart
I have a Pivot Chart with two series of data, one of which is plotted on the secondary y axis. Every time I refresh the chart, it defaults back and I lose my secondary axis. I then have to manually change this every time. Hi, This is a know issue. XL2000: Changing a PivotChart Removes Series Formatting http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q215904 Cheers Andy David wrote: > I have a Pivot Chart with two series of data, one of which is plotted on the > secondary y axis. Every time I refresh the chart, it defaults back and I > lose my secondary axis. I then...

CRM Integration: data doesn't go from CRM to GP
I'm setting up CRM integration for GP. Setup went ok, data migration from GP to CRM ok. When I add data to GP, it copies to CRM ok. I can't seem to get any CRM data to copy to GP. No messages in event viewer. Services are all started (according to documentation) successfully. I'm pretty sure I've configured properly in Settings and Admin tool. My IntegrationUser account is a member of all the required groups. I've turned on 'create customers always' to test, so I should create customers in GP whenever I create an account in CRM, but that's not happe...

Capture initial value of text box
Not sure why I can't seem to get this to work, but here's what I'm trying to do: I have a text box, 'Actual_Due_Date', tied to a field in a table. In the same table is a text box named 'Org_Due_Date'. When the initial value is set in the 'Actual' field, I want to capture and store it permanently in the 'Org' field. I have two forms, a New Project form and an Edit Project form. My thought was to just setup a simple macro in the New Project form in the 'Actual' field that says After Update set the 'Org' field equal to the 'A...