Graph that can switch data sets?

I don't know if this is possible, but i need a chart that shows result from a data set, and then if i click something, it shows a graph from another data set.

Is this possible with excel, or will i have to make separate charts for each data set i have?

Thanks

Hi there,

It's possible to do what you want by using VBA code to change the data series used by a chart, but it's not too straightforward & not too flexible either.

I normally take the opposite approach - instead of assigning my various data sets to a single CHART, I assign them to a single RANGE by using functions such as LOOKUP or INDIRECT to update this single range whenever a value (e.g. the month) in a particular cell is changed. The chart then receives its data from this single range.

A simple example - you have twelve monthly worksheets and you need to display graphed data on a monthly basis. Create a thirteenth worksheet. Insert INDIRECT functions on this worksheet so that it receives data from the monthly worksheets - e.g. if you enter 8 on in cell A1 of this thirteenth worksheet, the data for August is displayed. Then create a chart which uses this thirteenth worksheet as its data source.

You can then take this approach a stage further by placing buttons (e.g. one for each month) on the chart. These buttons trigger simple macros which insert the appropriate value in the cell used by the INDIRECT functions.

I've done this many times & have found that this approach works well. If you need any further information just let me know.

Hope this helps - please let me know how you get on.

Best regards,

Greg M

Hi there,

Ok - select Sheet1 & use Insert > Name > Define. This will display the names of one named range on Sheet1, and two formulas.

The formulas are the key to the method used. Select one of the formulas (e.g. "dates") & click on the "Refers to:" box - this will highlight a range of dates on Sheet1. The important thing about these formulas is that they are DYNAMIC - i.e. "dates" will always refer to the last twelve dates entered.

A similar approach is used for the "data" formula, but with the added feature of using the "Dept" value entered in cell A1 of Sheet2 - i.e. from the dropdown list.

The named range "depts" is the range containing the Dept names - i.e. B1:P1 - this is assigned as the list for the dropdown on Sheet2.

The dynamic data for the graph is contained in C1:N2 on Sheet2 - this data is taken directly from the "dates" & "data" ranges on Sheet1 & simply transposed using the TRANSPOSE array formula.

Hope this helps - please let me know how you get on.

Best regards,

Greg M

Sorry i took a while to reply, been really busy.

One difference with my data is that it doesn't actually need to stay limited to the last 12 months, it actually grows so new data is added, and this needs to be reflected in the graph.

What changes would i need to make for this??

Really appreciate the help mate

Hi Don,

Sorry - busy. Will get back to you on this one - shouldn't be too difficult.

Regards for the time being,

Greg M

Sorry just one more thing, in that example, i really do not know what causes the values on the sheet just above the actual graph to change when a department is selected from the drop down box.

Any help explaining this will be appreciated also.

Hi again Don,

I've re-jigged the original workbook a little, so that:

a) the worksheet names are "Data" and "Graph"

b) all non-blank data rows (not just the last year's) are included in the graph

c) the graph draws its data directly from the "Data" worksheet, not from a derived series on worksheet containing the graph.

As before, the "Data" worksheet contains one named range: "Depts", and two named formulas: "Dates" and "Data". These formulas actually define named ranges on the "Data" worksheet. The "Dates" formula is as follows:

Please Login or Register  to view this content.

This allows you to have up to 100 rows of data. If you need more than this, just edit the value to suit your requirements. You can be generous if you want to allow for future expansion (if you need 50 at the moment, provide for e.g. 150), but it's not considered good programming practice to include the entire worksheet (65536 rows) unless you really need them.

The "Data" formula (and therefore the "Data" named range) is derived from the "Dates" formula, the "Depts" range, and the value of the selected "Dept" in cell A1 of worksheet "Graph":

Please Login or Register  to view this content.

These named formulas define dynamic ranges, i.e. the defined ranges increase/decrease as data rows are added to/deleted from the "Data" worksheet. Note however that blank rows are not allowed WITHIN the ranges.

The graph takes its data from the named ranges "Dates" & "Data" - as these increase/decrease, so does the data range displayed on the graph.

Hope this helps - please let me know how you get on.

Best regards,

Greg M

Hi, thanks so much for the reply, the post and example really did help my understanding.

The only problem is when i am trying to recreate the file.

I copy and paste all the data over to my new file, define the names using the formula you gave, then make the drop down box,then finally the graph, but i can't get it to work.

I think the part that gets me is what data source to use when making the graph. I copied and pasted what was in yours, but it didn't work.

Also, say for example i use the list box and i did want the value to be shown on the same sheet as the graph, would this be difficult to do?

Sorry if i'm bugging you with loads of questions, any help is appreciated.

Hi again,

No problem about the questions. It's good to know that things are at least moving in the right direction.

If you want to post your workbook I'll take a look at it. If it's too big to post let me know & I'll PM you my email address so you can send it to me directly.

I don't really understand your question about the list box - can you give me a few more details about what you'd like to achieve?

Regards for the time being,

Greg M

With regards to the list box, what i was trying to say was, when selecting the department using the list in the graph sheet, say i wanted the values used in the graph to be shown on the graph sheet, like how it was in the original excel example. What amendment would need to be made?



I have attached my excel file. I simply tried to recreate your using your posts. My thoughts are that if i can successfully recreate your sheet, then i will be able to use the skills in other projects.

The problem arises getting the graph to update when i change the department using the list box.


Thanks!

Hi Don,

I think the attached is what you require.

To add the "Dept n" to the chart, just select the chart & use Chart Wizard > Next > Next, type any value (e.g. "My Title") in the "Chart Title" box & then click on "Finish"

Now select the chart title, type =, and then click on cell A1 (which contains the name of the selected department). This creates a dynamic title which updates in accordance with whatever department is selected. Now that the selected department is displayed as the chart title, you can probably delete the chart legend.

To link the chart to the data ranges, select the chart, use Chart Wizard > Next > Series, type "Data!Data" (without quotes) in the "Values:" box, and "Data!Dates" (without quotes) in the "Category (X) axis label:" box. Then click on "Finish".

You should now find that the chart updates automatically to include new data rows and to display those data for whatever department is selected.

Hope this helps - please let me know how you get on.

Best regards,

Greg M

Hi Don,

The code:

Please Login or Register  to view this content.

defines a range starting from Data!$A$2, offset by 0 rows, and 0 columns. The extent of the defined range is COUNTA(Data!$A$2:OFFSET(Data!$A$2, 0, 0, 100, 1)) rows and 1 column.


Within the above code:

Please Login or Register  to view this content.

counts the non-blank cells in the range starting at Data!$A$2, offset by 0 rows, and 0 columns. The extent of the range within which non-blank cells are counted is 100 rows and 1 column.

If you'd like me to take a look at your workbook regarding the "transposition" of dates & departments, you can post it here.

Hope this helps.

Regards,

Greg M