Complex Graphic combined with Macro for Dynamic Display

EE Pros,

I have a rather complex Graphic that measures a "gap" between two points/dates, that Teylyn originally built out for me and I have a complex macro that generates a Interval/Frequency table that Martin Liss and Fanpages built.  I'm now trying to combine the two.

First, the Interval/Frequency table is generated by selecting a Start Date, a number of Intervals, an Interval Metric and a Frequency Metric. From that, a table including an X1, Y1 and Y2 data points, is created (included in Range Name).  X1 is simple since it is actually Date/Time.  The Y1 and Y2 data is manually entered next based on the number of cells that get created for Y1/Y2.  

What I need to have happen, is to link the data table (X1, Y1, Y2) into the original model that Teylyn created.  By using the Range Names that are created from the Macro, I'm hoping to make the Graphic Dynamic so that as the cells expand or reduce given a particular scenario (from the Macro), the graphic will present correctly.

Thank you very much...... in advance.

B.
EarlyWarningAnalysisGraphic.xlsm
Bright01Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hi Bright,

just letting you know that I will take a look at this over the NZ weekend. I'm a bit busy while my work week is still active, but happy to spend time during the weekend to work on suggestions unless others pipe in.

cheers, teylyn
0
Martin LissOlder than dirtCommented:
If you replace your Worksheet_Change event with this, it will prevent problems from happening if a user adds rows or otherwise moves the Unit of Measure table.  Rows 7 to 13 were added and row 35 was changed.

If you can, please post a picture of what you want to see concerning the expanded or reduced cells.
Private Sub Worksheet_Change(ByVal Target As Range)
' Don't allow the choice of a Frequency Measure (C11) to be a longer
' time period than the Interval Metric (C10)
Dim strEndCol As String
Dim cel As Range
Dim bFound As Boolean
Dim rngUoM As Range

Set rngUoM = Cells.Find(What:="Unit of Measure", LookIn:=xlValues)
If rngUoM Is Nothing Then
    MsgBox "Unit of Measure table not found"
    Exit Sub
End If

If Not Intersect(Target, Range("C10")) Is Nothing Then
    With Target.Offset(1, 0).Validation
        .Delete
        Select Case Target
            Case "Second"
                strEndCol = "C"
            Case "Minute"
                strEndCol = "D"
            Case "Hour"
                strEndCol = "E"
            Case "Day"
                strEndCol = "F"
            Case "Week"
                strEndCol = "G"
            Case "Month"
                strEndCol = "H"
            Case "Year"
                strEndCol = "I"
        End Select
'        .Add Type:=xlValidateList, Formula1:="=C56:" & strEndCol & "56"
        .Add Type:=xlValidateList, Formula1:="=" & Split(Cells(1, rngUoM.Column + 1).Address, "$")(1) & rngUoM.Row & ":" & strEndCol & rngUoM.Row
        
        For Each cel In Range(Target.Offset(1, 0).Validation.Formula1)
            If cel.Value = Target.Offset(1, 0) Then
               bFound = True
               Exit For
            End If
        Next
        If Not bFound Then
            ' What's currenly in C11 is no longer valid because it's a longer time
            ' period than C10's time period so set it to what's in C10
            Range("C11").Value = Range("C10").Value
        End If
    End With
End If

' Create the data entry ranges
If Not Intersect(Target, Range("C8:C11")) Is Nothing Then
    CreateRanges
End If

End Sub

Open in new window

1
Martin LissOlder than dirtCommented:
Please see the change above, but I think it's better if you wait for teylyn for the rest of it.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Bright01Author Commented:
Got it!  Changed it.   Thank you!
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hi Bright,

I applied the changes suggested by Martin Liss, so we're on the same page.

As far as I can see, there is some VBA that creates a table with ranges for Y1 and Y2, depending on the options selected in column C.  If these ranges represent the data points to be plotted in the chart, I'd suggest starting range Y1 in cell F20 and Y2 in cell F22, instead of G9 and G10.  It is easy enough to use the already existing range names Y1_RANGE and Y2_RANGE in the series definition.

--> I've changed the VBA to write into these rows.

My major concern is the X axis and its values. Unless interval metric in C10 and frequency measure in C11 are the same unit, the X1_Range will refer only to the interval metric. Example:

If interval metric is Week and frequency measure is Day and we have four weeks in the time range, then the X axis range will contain the four week date stamps, but the Y ranges below will extend further to the right, containing a data point for each day.

Hence I think the X values must be populated with the units from the Frequency Measure, not the Interval Metric.

--> I've changed the code to create a X axis value for every data point.

--> I've changed the range name definition offset to apply in the order that the data is listed, i.e. Y1 and Y2 are located above the X values,  not below.

--> I've plugged the range names into the series definition of the chart.

--> I changed the data validation range for the drop-down lists for the alerts to X1_Range, so they list exactly the dates that are currently available in the chart.

--> I activated Martin Liss' code to apply the same number format to the X axis as has been selected with the button in B7 and applied the same number format to the data validation drop-downs in F23 and F24

--> I applied the Y1, Y2 and X1 range names to the Index/Match functions in F27 to G28

As far as I can see, the two range names Dataset1 and Dataset2 are no longer required. To be on the safe side, I've pointed the two range names to Y1 and Y2 respectively, in case you use them somewhere else.

I hope this does what you were planning to do. Please let me know if there's anything I have missed.

See attached.

cheers, teylyn
EarlyWarningAnalysisGraphic_teylyn.xlsm
0
Bright01Author Commented:
Wow!!!  Now that's an impressive piece of work.  I've got to test it tomorrow (it's early here) and read all of your comments.  

B.
0
Bright01Author Commented:
OK... here are my responses:

As far as I can see, there is some VBA that creates a table with ranges for Y1 and Y2, depending on the options selected in column C.  

-----------------------------------------------------
Yes.  And X1 is also created.  Think of it this way.  X1 is the Date/Time Interval variables that are created from Cells C8 (Start date), C9 (# of Intervals) and C10 (Interval Metric).  This gives you a series of Dates/Times the comprise X1 (or total Time).
-----------------------------------------------------

If these ranges represent the data points to be plotted in the chart, I'd suggest starting range Y1 in cell F20 and Y2 in cell F22, instead of G9 and G10.  It is easy enough to use the already existing range names Y1_RANGE and Y2_RANGE in the series definition.

------------------------------------------------------
Agree!
------------------------------------------------------

 --> I've changed the VBA to write into these rows.

 My major concern is the X axis and its values. Unless interval metric in C10 and frequency measure in C11 are the same unit, the X1_Range will refer only to the interval metric. Example:

 If interval metric is Week and frequency measure is Day and we have four weeks in the time range, then the X axis range will contain the four week date stamps, but the Y ranges below will extend further to the right, containing a data point for each day.

--------------------------------------------------------------

That is correct!  The Interval cells can and most likely (unless they are identical) will be less then the Y1 and Y2 Frequency cells.  So.... if the interval start is 08/2015, the Interval # is 3 and the Interval Metric is Months, there are 4 cells (incl. Start) for X1.  When you select the frequency, say Week, then Y1 and Y2 create 12 data/cell points for input.
-----------------------------------------------------------------------------

 Hence I think the X values must be populated with the units from the Frequency Measure, not the Interval Metric.

-----------------------------------------------------------------------------------

OK.... so the plotting of all the frequency cells on the X axis is true....but the actual dates for the X axis should be the Interval dates.  This is where it is complex.  So you would plot 12 X positions, but would only have 4 Interval Dates.  Make sense?
-----------------------------------------------------------------------------------

The other issue, is that if you change the Intervals, Interval #, Frequency, Frequency # or Start Date..... the graphic should change.
----------------------------------------------------------------------------------


 --> I've changed the code to create a X axis value for every data point.

 --> I've changed the range name definition offset to apply in the order that the data is listed, i.e. Y1 and Y2 are located above the X values,  not below.

 --> I've plugged the range names into the series definition of the chart.

 --> I changed the data validation range for the drop-down lists for the alerts to X1_Range, so they list exactly the dates that are currently available in the chart.

 --> I activated Martin Liss' code to apply the same number format to the X axis as has been selected with the button in B7 and applied the same number format to the data validation drop-downs in F23 and F24

 --> I applied the Y1, Y2 and X1 range names to the Index/Match functions in F27 to G28

 As far as I can see, the two range names Dataset1 and Dataset2 are no longer required. To be on the safe side, I've pointed the two range names to Y1 and Y2 respectively, in case you use them somewhere else.

 I hope this does what you were planning to do. Please let me know if there's anything I have missed.
D--Data-Data-Temp-EarlyWarningAnaly.xlsm
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hi Bright,

re this:

OK.... so the plotting of all the frequency cells on the X axis is true....but the actual dates for the X axis should be the Interval dates.  This is where it is complex.  So you would plot 12 X positions, but would only have 4 Interval Dates.  Make sense?

This can be achieved by showing X axis labels for only selected data points.

The problem with this, though is that the time units (month / week / days) don't perfectly match. With a time frame of 3 months, for example, where should the week markers sit? A month can have four or five weeks, or even touch 6 weeks.  Not every 1st day of the month will be a data point.

How do you want this to show?

We can probably work with a data series used just for labeling.  

I'll work on a suggestion.

cheers, teylyn
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
OK,

I've changed some of Martin's original code to create the range for a data series that will be plotted with 0 values only.  I added this range as a series to the X axis and set the series data labels to show below the data points.

The series is formatted with no line and a + sign as a data marker, so it looks like a tick point on the x axis. The actual x axis labels are hidden.

Give this a spin.

cheers, teylyn
EarlyWarningAnalysisGraphic_teylynV.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bright01Author Commented:
Fantastic work!  I'll be testing it more thoroughly but this has got to be a very complex graphic in combining with a macro and making it dynamic.  Thanks to you and Martin (and Fanpages) for the Teamwork in getting this to the level it's at.  Great work.

All the best,

B.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.