Producing individual charts

**jhelliar** · 01-28-2011, 05:39 AM

I am trying to put together a spreadsheet which has data for a large number of people. I have managed to adapt a macro so that it produces individual graphs for the people and I have a second macro which allows you to click on their name and it goes to the sheet which contains the graph. This therefore means I have a large workbook of 100s of graphs.

Does anyone think it would be possible to have a combined macro which would create their graph when you click on their name and a second macro on the graph to remove it when you have finished?

I have not attached a sample workbook at the moment as I just wanted to know if this is a possibility first.

Many thanks

Jack

**teylyn** · 01-28-2011, 05:43 AM

Hello Jack,

depending on how your data is organised, it might be possible to do this without any macros at all, using dynamic ranges.

Instead of 100 charts for 100 people, you could have just one chart, select the person from a drop-down data validation list and the chart will update to display the data for that person.

A sample workbook with data for half a dozen people in your original layout would help nail this down.

cheers,

**jhelliar** · 01-28-2011, 05:57 AM

Thank you.

I have attached a sample worrkbook which has some sample data and a template for how I would like each of the graphs to look.

Many thanks

**teylyn** · 01-29-2011, 01:50 AM

Hello, I've created these range names

MidyisData =OFFSET(Sheet6!$C$2:$R$2,MATCH(ShowName,Names,0),0)
Names =Sheet6!$B$3:INDEX(Sheet6!$B$3:$B$1000,MATCH("zzzz",Sheet6!$B$3:$B$1000,1))
ShowName =Sheet1!$A$2
Yr7Data =OFFSET(Sheet6!$S$2:$AH$2,MATCH(ShowName,Names,0),0)
Yr8Data =OFFSET(Sheet6!$AI$2:$AX$2,MATCH(ShowName,Names,0),0)
Yr9Data =OFFSET(Sheet6!$AY$2:$BN$2,MATCH(ShowName,Names,0),0)

I've changed the series reference in the chart on Sheet1 to reflect the range names.

Pick a name from the drop down list in A2 and the chart will show the data for that name.

cheers

**jhelliar** · 11-09-2011, 07:52 AM

This is very useful thank you but I have a separate sheet which contains a lot of data and what I need to be able to do is when you click on their surname it takes you to their Chart. I have got a macro which will take you to a chart of the same name as a cell I just need to have a macro which will produce the individual charts from the list of data or from a template chart. Or could this also be done using the dynamic ranges so when the name is clicked it produces their chart on a template and takes you to that template?

Many thanks for any help it would be very greatfully received

JAck

**teylyn** · 11-09-2011, 02:48 PM

Hello,

just as a general approach: Move the chart from the above file to the desired template, then set up a macro that does the following: If a name is clicked, it will be copied to the cell that feeds the chart selection, then activates the template.

Should be easy enough to do.