Filterable Dynamic Chart Range

Sep 3, 2008

Imagine I am plotting characteristics of a monopoly game: each row of my data represents one property and has a number of characteristics in columns. I have a scatter plot that draws several series from data in a filtered list. This lets me turn some of the points on and off by changing the filters, because the chart only plots the points that are visible. I'm remaking this chart using named ranges so that it will update each series automatically as the named ranges change. I'm doing this because I occasionally want to move datapoints (i.e. rows in the original data) from one series to another, and I don't want to manually go back and edit the series definitions in the chart. For example, if I am charting a monopoly game and each series represents one player, and each point is a property owned by that player, I want to account for the fact that properties may be traded between players.

I got the chart all set up using named ranges so that each series has one named range for the X values and one for the Y values. These are defined to include or exclude points from the list (think of it as the list of all the properties in monopoly) and it works great... except when I use filters on the source data (for instance, filter out the yellow properties) it no longer removes those points from the chart. Because the chart refers to the named range and the named range is immune to the filtering, changes in filtering do not update the chart. My chart named ranges are defined like this:

John_Properties_Cost = (All_Properties_Owner=$B$115)*Cost_Of_Property
John_Properties_Rent = (All_Properties_Owner=$B$115)*Rent_For_Property

I thought a good solution would be to "break up" the named range so that part of the calculation happens in the chart, like this:
instead of

named ranges defined as:
John_Properties_Cost = (All_Properties_Owner=$B$115)*Cost_Of_Property
John_Properties_Rent = (All_Properties_Owner=$B$115)*Rent_For_Property.........................

View 8 Replies


ADVERTISEMENT

How To Make A Chart Using A Dynamic Range

Apr 17, 2014

I have never used charts in excel unfortunately. I just finished watching a few tutorials but still a little confused. I have a WS with a list of items I want to make various charts of how do you make a chart that adjusts when new items are added? (thus dynamic data) Do you need a macro to do this or is it just standard by defining a range within that sheet?

View 7 Replies View Related

Chart Dynamic Data Range

Aug 31, 2006

I have created a chart that automatically expands as more data is added. This works great by using named ranges in my Chart's Series' Values and Axis Label fields. The named ranges use the Offset function.

The problem I am having is applying this same technique to to same chart to vary the number of series that I want to chart. I created a name range, using the offset function. But when I add the name to my Data Range formula, it will not stick. It changes the number of series displayed correctly when I add the name. But after I click OK on the Source Data dialog box, the data range reverts back to the address supplied by the name range variable; it does not retain the name range variable itself. Can Chart Data Ranges not contain a name range variable, especially when name range variables are used in the series formulas?

View 5 Replies View Related

Plot Chart With Dynamic Range

Jun 27, 2008

I maintain data which gets updated every week. I have a chart associated with this data which I want to get updated automatically. I tried to use dynamic range on the chart and it kinda works. But the issue with my data is that I have data set in a row, followed by a blank cell which is then followed by average of last two weeks. Hence, when I use a dynamic range, it also displays the last value in the row (average), which I don't want to display in my chart.

So what I am looking forward to do is set up dynamic range or any other solution, which automatically updates my chart with weekly sales data without showing the average value in the chart. I have attached sample worksheet here.

View 4 Replies View Related

Create Dynamic Range With Updating Chart

Jul 8, 2014

I have created a spreadsheet which uses data validation to allow a user to select a company, start Date, end date, and projected months in order to output different figures. I have attached a spreadsheet to this post in case that was hard to understand.

Since there is data validation, indexing, and matching going on, as different selections are made, various tables change as a result. Uou will be able to see what I mean as you open the example sheet and start messing around with the data validation boxes.

The problem I am encountering now, is creating an exponential graph that will also keep changing as the user changes their selections in the data validation boxes. I am unsure if this is even possible, but I figured it was worth a shot to ask. On the example sheet, I have included a graph similar to the one I want, but it only contains data in a certain range of cells, rather than dynamically changing to accommodate whatever data is outputted.

Here is what I am looking for in the graph:
1. Whatever dates are selected by the user in the data validation boxes, along with the projected dates will be on the x-axis.
2. The PMPM figures associated with the selected dates, and the projected dates on the y-axis.
3. Ability to make different selections from the data validation boxes and still have a dynamic chart that keeps updating itself.

View 1 Replies View Related

Line Chart From Dynamic Range Of Data

Feb 14, 2014

I have a spreadsheet that I have setup to have raw data pasted into. Long story short, it pulls the maximum reading for a given 5 minute period and returns the highest value for each 5 minute increment where there was a reading. It does this for all of the different dates entered, returning a separate data table for each date.

What I now need is to graph the data on a line chart. Since the number of time values can be variable, I have to include the entire range (for example A3:B5002). When I graph this out, it has all of the data clumped up at the beginning and flatlines for 99% of the chart.

I tried building dynamic named ranges using offset, but that seems to yield similar results once charted.

See the link below where I've dumped some sample data in (it wouldn't let me upload due to the size and I can't upload only part of it without breaking the whole thing). The data I need to chart is from columns A&B on the GraphData sheet. Sheet 1 contains the chart I have been trying to use. The endgame of this is to have the end user paste in their data and *POOF* charts.

My only restriction is that I cannot have Macros/VBA.

[URL]....

I tried following this post and using NA() instead of ""...no dice, it just puts #NA at the bottom labels instead of spaces.

View 3 Replies View Related

Chart Won't Accept Dynamic Named Range

Sep 9, 2011

I'm setting something that allows the user to compare bond yield curves on a graph. The setup involves a dropdown to select the final year of data the user wants to see and several date drop-downs.

For example, if the user wants to compare the current yield curve out to 2031 against how the same curve looked the end of Q1 2011 and Q2 2011, he would select "2031" in the first dropdown and then select "4/1/2011", "7/1/2011", and "9/8/2011" in the other three dropdowns.

The data is on a separate sheet, and mechanics of my named ranges are sound. However, Excel won't allow me to add my named ranges to my graph.

As is, the series looks like this:

=SERIES($L$5,'2011'!$B$3:$B$25,'2011'!$C$3:$C$25,1)

I'm trying to replace $B$3:$B$25 with CRV_DATES and $C$3:$C$25 with CRV_ONE (when I get this to work I'll add the other curve to the chart). When I try to add them, though, I can't get out of the series dialogue. When I hit enter, nothing happens--no error message, no nothing. The change simply won't "stick".

Here are my primary named ranges and the ranges/functions I use within them:

CRV_DATES: =OFFSET('2011'!$B$3,0,0,FindCurve,1)
CRV_ONE: =OFFSET('2011'!$C$2,1,MATCH(DATE_ONE,StartDateRange,0)-1,FindCurve,1)

FindCurve: =MATCH(CRV_END,MaturityRange,0)
StartDateRange: =OFFSET('2011'!$C$2,0,0,1,COUNTA('2011'!$2:$2))
MaturityRange: =OFFSET('2011'!$B$3,0,0,30,1)
CRV_END: =Sheet1!$L$3

View 2 Replies View Related

Chart Labels With Dynamic Named Range?

May 31, 2012

I have created a chart that provides a hisotry of events on a timeline. It relies on named ranges and allows me to change the upper and lower date ranges displayed on the x-axis of the chart by changing the date in cells that define the upper and lower dates. In order to display the events on the timeline I have a table that includes 3 columns... a date colmn, an event description column, and a event height column. To display the information I have used a line chart with data points that uses the "event height" data to determine how high up on the graph to display the event information. I have hidden the line, added a label for each event, and added an error bar that draws a line between each label and the bottom of the chart. By default the labels displayed the "event height" information (as expected), so I selected each label individually and added a formula that causes it to display information from the "event description" column instead of the default value. All of this works perfectly with no issues.

The problem comes when I change the date range displayed in the chart. Currently the chart displays event information for 1/1/2012 - 5/30/2012. I can modify 5/30/2012 to be any date I would like that is greater than 1/1/2012 with no problem. Everything displays correctly. However, if I modify the 1/1/2012 date the labels change to reflect the original value (event height data) instead of what I modified it to (event description).

[URL]

View 1 Replies View Related

Dynamic Range In Chart Reverts To Static Value?

Jun 7, 2012

I created a named range linked to a slider.

Code:
=OFFSET('Slides'!$B$2,0,0,6,'Slides'!$AB$1+1)

Problem is after I put in the range as the chart data range it reverts to -

Code:
='Slides'!$B$2:$H$7

View 1 Replies View Related

Chart Series Does Not Accept Dynamic Range

Nov 26, 2006

Using the Dynamic Named Range tip, I defined named variables as follows:

Xrange=OFFSET($A$2,0,0,COUNT($A:$A),1)
Yrange=OFFSET($B$2,0,0,COUNT($B:$B),1)

then tried to define my Chart series as follows:

=SERIES($B$1,Xrange,Yrange,1)

but it gives an invalid reference error. What am I doing wrong?

View 2 Replies View Related

Dynamic Named Range In Chart Data Series

Nov 30, 2006

I am attempting to use a dynamic named range in a chart data series as described in Excel Hacks (Hack #42 and Hack #52).

Using Insert|Name|Define I have created a named range called CashFlowSaleChartDataRange that is set to the following value:

=OFFSET('Cash Flow-Sale'!$O$10,0,0,NumFlows,1)

When I subsequently assign a reference to this named range in the data series dialog it will accept the answer and my chart will adjust to reflect the updated range. However, if I return to the data series dialog, I now see the range address returned by the OFFSET function rather than the named range reference.

View 2 Replies View Related

Function Is Not Valid On Dynamic Named Range Chart

Apr 20, 2007

I have seen one of your posts in which you make a graph update based on dynamic named ranges. Here is my problem, I already defined the name of my ranges as:

ChurnDiario =OFFSET(Clientes!$E$25,0,0,1, COUNTA(Clientes!$E$25:$AH$25))
Ejex =OFFSET(Clientes!$E$8,0,0,1,COUNTA(Clientes!$E$8:$AH$8))

The issue is that when I tried to enter the values and category (x) axis with:

Values: =offset(ChurnDiario,0,0)
Category (X) axis labels: = offset(Ejex,0,0)

On both, excel replies “That function is not valid”. I’ve tried, but still can’t make it work.

View 2 Replies View Related

Dynamic Named Range As Chart Source Data

Jun 16, 2008

I've read through every thread I can find about dynamic named ranges as source data for a chart. It seems as if everyone can get it to work but me. I have a chart on one sheet that needs to update dynamically from data on another sheet when a button is clicked. Here's my code that runs when I click the button:

Sub Update_Center_Chart()
Sheets("Center Data Chart").Select
ActiveSheet.ChartObjects("Center Data").Activate
ActiveChart.SetSourceData Source = Range("CenterData")
End Sub

I get the error message: "Type mismatch." The data is in B1:F2 on a different sheet. The formula in the named range, "CenterData" is this:=OFFSET('Center Data'!$B$1,0,0,COUNT('Center Data'!$B:$B),4). The data will expand by 1 row every time.

View 5 Replies View Related

Excel 2003 :: Dynamic Named Range Of Non-contiguous Cells Suitable For Chart Series Reference

Nov 11, 2012

I am running Excel 2003 on a Win7 system.

Here is my situation:

Each of my data sets spans roughly 75 columns by 250 rows at present, but this could expand. The first 7 rows contain metadata. Columns 2-25 or so contain the raw data, from which everything to the right is calculated. The data sets have most columns in common, but not necessarily all.

In order to tease out the most meaningful information from my data, I frequently sort all or part of it based on varying criteria. When I find a useful sorting criterion, I create a new column with a header that describes the criterion and populate it with a formula that returns a 1 if the condition of interest is met for that row, or a 0 if it is not. For example, if I am doing this in column AA, I might enter

=--(AND($AX8>$AA$4,$Y8>0))

and copy it down to the end of the data. The resulting vector of 1s and 0s quickly re-identify data that meets that criterion even after subsequent resorting. It also makes locating data that meets multiple sorting criteria extremely simple. Essentially, I create a truth table.

Cell $AA$4 in the above example contains a "comparator" value I might wish to change at some point, which would change the subset of data the condition selects for.

Here's the first hard part:

For each data set, I need the ability to generate meaningful plots that includes separate series based on the criteria I have described. However, I also need to retain the ability to resort the data or change the comparator value without disrupting these plots. In other words, the plots must NOT change when the order of the data is changed, but MUST change to display the appropriate data when the comparator changes.

Here's the 2nd hard part:

Once I have this working for one data set, I need to be able to port it to other data sets (which are contained in other workbooks), so that I can compare equivalent plots from each. I also need to minimize the number of manual steps involved in doing so, to avoid human errors and excessive time consumption.

The only other possible complication I can think of at the moment is that, to this point, I have been inserting blank rows to isolate subsets that I do not wish to perform further sorting on from each other.

Right now I am angling toward VBA code that loops through the entire data set to generate base dynamic ranges using the column header row (row 1) as the names, and the entire column of data for the rangeloops through the truth table columns to generate "branch" row ranges for each of the sorting conditions,loops through the entire data set one more time to create "branch" ranges for each of the base ranges.

I could generate some code to accomplish a one-off solution for a given configuration of a single data set (provided there is not a list length limit in a chart series that I'd be violating)...but without a dynamic named range, I don't know how to get to something that would update appropriately. So in essence, I am still stuck at the dynamic range part of this.

View 1 Replies View Related

Hide Cells But Still Filterable?

May 17, 2014

Is it possible to hide ("make the text invisible") cells, but if you filter the column, it will still filter on the data inside the hidden cell but not on the data you only see? I want to hide the cell data for layout purposes.

View 2 Replies View Related

How To Name A Dynamic Range & Make A Validation List (of 2 Dynamic Ranges)

Dec 22, 2009

I have a range which will change in size & in content, & I want this to be a Named Range at whatever size it is.

Reason I want to is because I want to make a Validation List with this dynamic range. I also want a Validation list which lists the content of 2 or more dynamic ranges which may or may not be on the same worksheet - is this possible?

i.e.
First dynamic range: called "Milestones" at A11
Second dynamic range: called "Activities" at A25
& make a Validation list that will list content of both

View 9 Replies View Related

Pivot Table With Dynamic, Updatable Chart, But Not A Pivot Chart!

Dec 19, 2008

My boss wants me to design a dynamic, updatable chart in Excel 2003. I initially made a Pivot Chart based on a Pivot Table which worked perfectly, but it doesn't look professional enough when printed (or viewed) and she wants me to approach it a different way.

So, I created a graph based on the data in a Pivot Table, and used dynamic ranges as the source for the graph series so that the chart updates when the criteria fields are changed for the Pivot Table. I then added two combo boxes (ie data validation lists) to the Chart sheet, and wrote VBA code so that whenever the combo box values are changed, the Criteria fields for the Pivot Table on the 2nd sheet are updated accordingly, and this in turn causes the graph to be updated as well.

This solution also worked perfectly, but now I've been told to create the graph without macros.

Does anyone have any suggestions? The requirements/details are as follows:

1. The Pivot Table is on sheet "PIVOT", and the graph is on sheet "GRAPH"
2. The Pivot Table has two criteria - School Name and Year Level
3. On sheet "GRAPH" there are two data-validated fields, School and Year, which only allow the selection of valid Schools and Year Levels

Is there any way to make the Pivot Table update when values are changed in the fields on the CHART sheet so that the chart also updates, but without using code nor a Pivot Chart?

View 9 Replies View Related

Dynamic Bar Chart

May 23, 2007

I'd like a bar chart that will only display bars for positive values. So, if a cell has 0, it won't display anything. I.E. - if i have 10 fields and only 5 of them have values greater than 0, my chart will only display 5 bars instead of 5 bars and 5 empty fileds. kind of like piezeros, but for a bar chart.

View 9 Replies View Related

Dynamic Chart

Aug 20, 2007

I am trying to create a bar graph from a table which is pasted below. Two important things here are :

1. The range of the data for the graph are not adjacent. For the chart, the range will be columns D, F,H and J.

2. The range of the chart will not be static. As per the table below, it is uptill August but next month another row will be inserted and the range will increase by 1 row.

View 7 Replies View Related

Dynamic Chart Legend Using VBA?

Jun 28, 2005

I am creating a chart where the number of data series used on the chart will change every time the code runs depending on what information people are looking for. That part works fine. I need a legend that shows which data series were added to the chart. Is there a way to get a dynamic legend on the chart that matches the data series, without manually adding it?

View 6 Replies View Related

Dynamic Chart For Data

Jan 30, 2014

make it working, tried from online thread but unable to get it done.Dynamic.xlsx

View 5 Replies View Related

More Dynamic To Rolling Chart?

Feb 5, 2014

[URL]

Alas I need to put more dynamics and fill this data set from other sheets, so I'm putting formula into column A, and this force chart react and insert another category.

In other words I want to have this fill only if data present on different tab(worksheet).

That what I have now:

[Code] ........

COUNTA
Counts the number of cells that are not empty and the values within the list of arguments. Use COUNTA to count the number of cells that contain data in a range or array.

View 2 Replies View Related

Dynamic Pie Chart With Control Box?

Feb 6, 2014

(I am using different data IRL which is company sensitive so I have designed a simple example spreadsheet.)

Ok, So I have a list of words A2:A4 and I have created a Control Box which allows me to select which word I want from the list.

On a separate tab I have lists of data which relates to individual words from the A2:A4 list.

What I what to achieve is when the word is selected from the drop down menu it will create a Pie chart showing me the break down of percentages and attributing 'companies'.

View 6 Replies View Related

Dynamic Chart Failure

Jun 6, 2008

1. I'm using Excel 2007

2. I'm trying to create a dynamic chart - following instructions from the numerous other threads on the topic

3. When I try to use a dynamic named range in the "Select Data Source" box, it creates a fatal error and the program shuts down.

View 9 Replies View Related

Chart Dynamic Ranges

Jan 20, 2010

I have a stock chart that I want to update dynamically if the data record length changes. From the source data menu, I tried to use the following data ranges:

=OFFSET(Candles!$AB$8,1,0,COUNTA($AB:$AB)) ---> Date, x values
=OFFSET(Candles!$AD$8,1,0,COUNTA($AD:$AD)) ---> High
=OFFSET(Candles!$AE$8,1,0,COUNTA($AE:$AE)) ---> Low
=OFFSET(Candles!$AF$8,1,0,COUNTA($AF:$AF)) ---> Close

When I place any of these in the data range box under 'x axis labels or values', I receive the message "This function is not valid".

The MS webpage at http://office.microsoft.com/en-us/ex...098011033.aspx describes using these formulas but they don't appear to work.

View 9 Replies View Related

Dynamic Stacked Bar Chart

Apr 28, 2006

I am trying to develop a dynamic stacked bar chart, I have a consistent number of columns defining the x axis and a variable number of rows defining the y axis (the stacked data). Is it possible to generate a dynamic stacked chart. I have tried using dynamic named ranges but I can't get it to work for a stacked chart.

View 7 Replies View Related

Dynamic Pie Chart With No Zeros

Jul 11, 2007

Please see the attached spreadsheet, I would like to create a dynamic pie chart shows the monthly strategy breakdown (one month at a time), but not showing the zero value and also a drop down box to be able to choose which month's strategy breakdown to display on the chart.

View 9 Replies View Related

Dynamic Chart Examples

Feb 5, 2008

I have a template I used where, over the course of a month, I add an additional row of information (the 31 rows are there, just the information is inserted with macros every day). The charts, though, are daily, meaning that every day I need the charts to show information from the last row entered. How do I instruct a chart to take the last row of information and take the data from there (and automatically create the chart) instead of having to do it manually?

View 2 Replies View Related

Dynamic Pareto Chart

Feb 8, 2008

I have data in a matrix captured for the different machines we have. I need to make a pareto chart for the PPM's in each machine. This is updated everyday so i recorded a very primitive macro just to avoid making the 15 graphs i need as i have to filter the zero cells, then sort them descending and then making the graph....
The only way of making this in a more lean way is using a vba code?? do you know an already made code i can use for it?? i've searched the site with no luck. Here's a brief example of what i have:

Machine 1 Machine 2 Machine 3
Total PPM Total PPM Total PPM
Defect 1 30 4.5 0 0 0 0
Defect 2 0 0 0 0 0 0
Defect 3 0 0 45 20.4 98 298
Defect 4 22 12 0 0 36 123

I need the pareto chart for every machine's PPMs, sorted descending and as u can see some machines share defects.....

View 5 Replies View Related

Scrolling Dynamic Chart

Mar 14, 2008

I have graphed a range of data that was generated using VB, but the data is a series of 6 models plotted against each other over a period of 6 years and the data is daily and as such would be useful to reduce the range to look at certain periods , is it possible to do this using the mouse to select a region or would something like embeded text boxes for the ranges of dates the user could enter be better, or would it be better to move away from excel for this part of it be better. Also is it possible to plot say range A1:A5 and A20:A25 against the the relevent dates to examine two separate periods side on side.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved