interactive charts

**danhenshy23** · 11-07-2010, 06:54 PM

Hello,

I am wanting to display data from different sheets in chart form on sheet 1. A drop down list will also need to be linked to the chart in order to select which data is to be displayed.

After reading various help guides and blogs, I now understand that I need to use interactive charts.

I found a guide similar to what I want to achieve at http://peltiertech.com/Excel/Charts/ChartByControl.html, however he is using dual-lists and was a bit too complicated to follow.

Basically, I will have different data for different people on different worksheets. On sheet 1 I want to be able to choose which person's data is displayed.

Is anyone able to help me with how I would achieve this?

Cheers

**teylyn** · 11-07-2010, 07:18 PM

Hello,

This depends a lot on your data layout, so a sample workbook would be most helpful.

Attached find a possible solution where each person's data is named as a named range, and the data used for the chart is using an Indirect formula to pull the person's data into Sheet 1.

There are many possibilities how this can be achieved, but without knowing your specific data layout, it's hard to recommend something.

**danhenshy23** · 11-07-2010, 07:40 PM

Ok, here is what I'm currently working with.

I have a spreadsheet for each individual which looks like the example attached.

My current method means that I am having 20 plus different documents which need updating with new data on a weekly basis, which is very time consuming.

My plan is to consolidate into 1 file by having each individual's information on sepearte sheets rather than documents. Sheet 1 will then be used as a type of home page to display the data in graphs, like shown in the example.

I realise that with me wanting to link 5 different charts to 1 individual, then this might mean that 2 drop downs are required. i.e names in one list, acw, hold, transfer, wrap and not ready in the other?

As new data becomes available, then I will still have to update each individual's sheet, however I would much rather this than have to update 20 different documents.

**danhenshy23** · 11-07-2010, 07:44 PM

Sorry is that's still a bit unclear.

So basically 'Stats RAW data' would become 'Steve's data' for example. Then there would be 'John's data', and 'Paul's data' for example as sheets in the same layout

**teylyn** · 11-07-2010, 08:32 PM

Hello,

if all the sheets have the same structure, you can use the approach I described above. The chart data is in the Stats RAW DATA sheet, where it is pulled with the Indirect formula.

see attached.

Can you please update your profile to reflect your Excel version? This will make it easier to give you an answer that suits your version for questions where there's no file attached.

cheers

**danhenshy23** · 11-08-2010, 03:16 AM

Wow that's fantastic! Thank you.

Now I know that it can work, I need to try and understand the process you followed to put that together so that I can add to it and maintain it.

I have added on another sheet and mocked up some data. I have also updated the list menu with this person's name.
Now how do I link the data to the RAW DATA sheet, so that it pulls through into the graphs on sheet 1?

Thanks for your help on this

**teylyn** · 11-08-2010, 04:02 AM

please study the example file.

Each sheet has the data in exactly the same place. In each sheet, a range name has been defined with the person's name. When a person's name is selected in the dropdown box on the first sheet, the data will be pulled into the second sheet with the Indirect() function.

You can add more sheets for Tom, Sue and Harry. Make sure they have the same structure as the other sheets. Then create range names called Tom, Sue and Harry, making sure to size them exactly like the other range names.

Finally, add Tom, Sue and Harry to the data validation list on the first sheet.

**teylyn** · 11-08-2010, 04:22 AM

Note: If your data is not in exactly the same cells in all the sheets, the formula on the second sheet needs to be changed to accommodate this. It is possible. Just let's know if it's needed.