How to 'Drill Down' from Excel Scatter Chart to Data Table? 1

[RichardWale]

I have an Excel file that creates a standard 2-axis 'Scatter' chart from a separate Excel worksheet, within the same workbook. If I hover over any of the points I can see the co-ordinates that have created it, but would like to be able to 'click' on the point to get back directly to the underlying data table, in order to identify the actual data record itself, i.e. 'Drill Down' from the chart point to the data source. There can be anything up to a maximum 15,000 scatter points, but more typically up to 3,000.

Is there a way of doing this?

Richard

 

[SkipVought]

Hi,

Yes there is, but only via VBA. Plz post this question in forum707 where members go for these kinds of solutions.



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

BTW, is your chart in a worksheet or is it a chart sheet?

Specifically, how do you want the drill down to be handled in the source data table?
Do you want the table FILTERED on the values?
Do you want the coordinate pair highlighted?

Please specify exactly what behavior you want.

Please answer all these questions.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[RichardWale]

Skip,

Thanks for your instant reply!

The chart is a separate chart sheet.

The Excel file is an analysis tool for inventory and the scatter chart is constructed by a macro from a report run within the tool.

I want the drill-down to find the record reference, which is a 'part number', that has the co-ordinates of a chosen scatter point, and once that record is identified I can run an existing macro that displays all the characteristics of the record as an 'Item Enquiry' screen. By selecting a scatter point on the chart the user will be able to see all the characteristics of the part number associated with the scatter point.

No, I do not need or want any data filtering, just to identify the record.

I don't need the co-ordinate pair highlighted, just the associated record reference.

I have extracted out the 2 sheets that comprise the scatter chart - the data and chart sheet - and attached it to this post. A macro extracts data from the report into Columns A - I, part of which is transposed by the macro into rows 1 - 5, starting at column O. The number of rows extracted from the report varies from 0 to a maximum of ~16,000 (limited by the number of columns in a .xlsx worksheet!).

The chart source data is contained in rows 1 - 5 from column O to the last item column + 1. The record that I want to identify is the cell in row 1 that is associated with the co-ordinate pair in rows 2 & 3 in the same column.

Hope that all makes some kind of sense??

Should I still post this in the forum707: VBA Visual Basic for Applications (Microsoft)?

 

[SkipVought]

I modified your workbook as follows:

1) Deleted all named ranges as they all had broken references

2) Added new named ranges for the chart source data table based on the names in the LH column. (I wonder why you have the Base column???)

Added this code to display the Item corresponding to the selected data point in the chart...

Option Explicit

Private Sub Chart_MouseDown(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
'SkipVought 2016 Nov 18
'click on a data point and observe the Item in a MsgBox pop up
    Dim IDNum As Long
    Dim a As Long
    Dim b As Long
    Dim xl As Application, ws As Worksheet
    
    Set xl = Application
    Set ws = Sheets("Cosmos Data")
        
    ActiveChart.GetChartElement x, y, IDNum, a, b
    If IDNum = xlSeries Then
        MsgBox "Item = " & xl.Index(ws.Range("Item"), 1, b)
    End If
    
    Set ws = Nothing
    Set xl = Nothing
End Sub

For the VBA wonks, like me, the key to this feature is GetChartElement. Since this chart is a Chart Sheet, the events are exposed. For an embedded chart, you will b=need to add a Class Module to expose chart events. John Walkenbach has an excellent Excel Charts, a great reference for your library.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[RichardWale]

Hi Skip,

Oh wow! That is great! Exactly what I needed, thank you so much.

From what you have done I can extend the code to use the 'Item' code now identified, as the input into another, existing, macro to display the 'Item Enquiry' screen for the selected item.

The 'Base' and 'End' columns are added by the macro after the data transposition to make sure that, even with no items being selected to display on the 'Cosmos Chart', the 2 limit lines, Safety Stk and Max Stk, display correctly.

Do I need all 5 of the new names to be updated by the macro that currently creates the Cosmos Chart? Are they all used?

Thank you again, Richard

 

[RichardWale]

Hi Skip,

All looking great. I have incorporated your code as a new macro in the Stock Model, extended it so that the 'Item' that is identified from it is used as the input into the 'Item Enquiry' screen macro, and have added the dynamic update of the 'Item_Ref' range (was 'Item', but I had to change it to 'Item Ref' as 'Item' already exists as a range) into the new macro code.

Are any of the other 4 new ranges required?

I did NOT copy over the macro to delete all the 'Names'!

Thanks again, Richard

 

[SkipVought]

The Item named range is the only range that the Mouse_Down event uses.

I uploaded a new version that adds code for an event in the Cosmo Data Sheet, that when any change occurs in data values, the Item range (and the other 4 ranges) will be recalculated as required by current data.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[RichardWale]

Hi Skip,

Thanks again. I will look to incorporate that as well.

The Cosmos chart was a fairly recent addition to the inventory model and provides a very quick visual interpretation of the overall profile, but just begged for the ability to click on a chart data point and be able to find out more detail - now I can!

Richard