Drill Down in Excel X,Y Chart 2

[martinjt]

Is there a way to combine an x,y chart with a pivot table, to drill down? I know that the x,y chart (scatter) is not an option to use with pivot tables. What I need is to be able to click on a coordinate in the x,y chart and see the individuals who have that x,y value. For instance

x,y value name
1,1 indiv1
indiv2
indiv3
indiv4
2,1 indiv5
indiv6
indiv7
Thanks,
JT

 

[SkipVought]

Hi,

Not without some fancy VBA. Post in forum707 if you care to pursue.

Here's the general approch. Depending WHERE your chart is, in a SHEET or as a Chart Sheet, you may need to code a Class Module to expose Chart Events.

Once you can get chart events, clicking on a point would set Criteria in your Source Data AutoFilter, for instance.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlhelp]

Not in a chart, but in a spreadsheet, you can do this with conditional formatting. Set up your table (Say A2:B8). Make Indiv1 etc to be white font so they are not visible. Pick a cell (say C1), Place 1,1 or I used 11. Highlight B2:B8. Go to conditional formatting and set formula is =A2=$C$2, and set your format to Black font.

Then whatever you type in cell C1 lights up the values you are looking for.

Canadian eh! Check out the new social forum Tek-Tips in Canada.
With the state of the world today, monkeys should get grossly insulted when humans claim to be their decendents

 

[martinjt]

Thanks Skip. I thought that might be the case. I may venture further but that forum already has some helpful pointers in that regard.

xlhelp - thanks. It will need to be a scatter chart. - JT

 

[SkipVought]

You could take xlhelp's suggestion regarding Conditional Formatting and manipulate that, based on the point selection. It would probably play in Peoria, better than AutoFilter.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[martinjt]

Thanks, point selection? Do you mean you can do conditional formatting on a chart object/click event? Or are you just speaking of the spreadsheet?

 

[SkipVought]

1) The CHART needs to expose EVENTS.

2) Once capturing the selection of a point in the chart, the appropriate criteria can be applied to Conditional Formatting on the SHEET.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlhelp]

Unfortunately, I am not exactly clear, as to what yur ultimate goal is. Anyway, there is this neat little trick that I use in a couple of my charts. It's dynamic charting that works so well.

What you do is set up your data in a table. Plot your chart for the whole set of data. Then in your data table turn on auto-filter and choose your filter criteria. The chart changes as you switch your data with autofilter.

Canadian eh! Check out the new social forum Tek-Tips in Canada.
With the state of the world today, monkeys should get grossly insulted when humans claim to be their decendents

 

[martinjt]

You two are awesome! I will expose events if necessary but with xlhelp's help, I believe the chart/filtering combination works perfectly as is. If the chart comes back with a need for the point selection, I'll go that route. Thanks again,
JT