Filtering Data and changing data range from graph? (Excel 2007)

auroralily

New Member
Joined
Sep 13, 2014
Messages
24
Hi,

I have a lot of data on students in 2 sheets. One called GraphData:
datascn.png


The other called TargetData:
targetdatascn.png


I have a graph that plots a line with no markers for the cells H2:U2 from TargetData and plots another line for the same cells in GraphData. The formatting of the line for the GraphData series is set up so points in a PC column are invisible:
graphscn.png


I would like to add code to the graph so a user can do the following straight from the graph:
  • Click the select group button to filter the data based on the contents of Row B in GraphData
  • Upon selecting the data the chart will show the Flight Plan (from TargetData) and test grades (from GraphData) keeping the formatting as set above and the title is selected from TargetData (eg TargetData!$A$2)
  • When the button 'Next Student' is clicked the chart changes the data series and title to the next row in the filtered data (eg changes from TargetData!$H$2:$U$2 to TargetData!$H$3:$U$3)
  • If the student is the last one in the data set clicking 'Next student' show a message 'You have reached the last student in the list - plotting data for student 1' and the when ok on that message is clicked the chart shows the data for the first student in the list.
  • When the button 'Previous Student' is clicked the chart changes the data series and title to the previous row in the filtered data (eg changes from TargetData!$H$3:$U$3 to TargetData!$H$2:$U$2)
  • If the student is the first one in the data set clicking 'Previous student' show a message 'This is the first student in the list - plotting data for last student in list' and the when ok on that message is clicked the chart shows the data for the last student in the list.

I would also like to use the other buttons to apply additional filters:

  • Only one filter (in addition to the group filter) can be applied
  • When pressing button 'Filter X' (eg Filter PPI) is adds an additional filter to the data set depending on the relevent row (eg 'N' in Row F for 'Filter PPI')
  • Once I have the base code for one button I can apply it to the other filter buttons myself - just need the starting point.

Possible extra feature but no idea if it is possible:
  • When a button (not included on pic) is pressed all charts for that data set (eg all students in 11l1) are exported to one word document

I've tried searching forums but couldn't figure out where to start. Apologies for the long question - hopefully it is sufficient in describing what I would like to do but I'm happy to clarify if not

Thanks
 
Last edited:

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Forum statistics

Threads
1,222,414
Messages
6,165,891
Members
451,992
Latest member
kaurmanjodh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top