Dynamic Charting from disparate data

[crmauch]

I have worked with Excel for years, but I’ve never done much with Excel around Lookup functions or INDEX/MATCH and charting (if that is even the right approach). Either formula or VBA approaches are worth considering

There is a sheet that lists multiple jobs. There are dates and hours scheduled for the tasks of printing and bindery (Simplified example):

Jobs	Digital Print Date	Print Hours	Bindery Date	Bindery Hours
1	6/3/2015	4.00	6/4/2015	3.00
2	6/4/2015	3.00	6/5/2015	4.00
3	6/3/2015	5.00	6/4/2015	5.00
4	6/4/2015	2.00	6/4/2015	6.00
5	6/6/2015	4.00	6/6/2015	2.00
6	6/6/2015	2.00	6/7/2015	4.00

<tbody>
</tbody>

I believe that I need to read this data into another table that would (I would use the word ‘collate’ the data ) (note that there was no data for the Print hours for June 5^th) look like this:

Date	Digital Print Hours	Bindery Hours
6/3/2015	8.00	0.00
6/4/2015	5.00	14.00
6/5/2015	0.00	4.00
6/6/2015	6.0	2.00
6/7/2015	0.00	4.00

<tbody>
</tbody>

Out of this data, I would create a chart that would show each date, with two bars, one for the print hours and one for the bindery hours.

This would need to be dynamic. As new jobs were added with their dates, the hours would be recalculated and the chart redrawn.

I think if I can get the data into the 2^nd table format, I can make the chart, but I’m concerned about how to make it dynamic.

Any input is appreciated.

 

[Worf]

Hello and welcome to the Board

See if this helps: Create a Dynamic Chart

 

[crmauch]

I came up with my own solution (with help from numerous sites!!!):

First I copied all the relevant data using the Cell formula and a defined range:

=IF(CELL("contents",OFFSET(Test2.xlsm!Digital_Print_Date,I4,-2))=0,"",CELL("contents",OFFSET(Test2.xlsm!Digital_Print_Date,I4,-2)))

I had 6 columns copied using this (copied for 100 rows), as an additional date and hours column of data that needed added to the Print hours data.

Then using the following sites: Building a self sorting list/ and Building a self sorting list Part 2/

I created 3 seperate sorted and unique list of dates. (Note, I think there are sites that do this more elegantly and succinctly, but the advantage for me was I could understand the code and modify it for my purposes. Some of the other sites have very complex formulas, and I could not get that code to work.)

Then I merged the list of dates into one column:

=IF(ROW()-3<=COUNT_WW,INDEX(W:W,ROW(),1),IF(ROW()-(3+COUNT_WW)<=COUNT_AEAE,INDEX(AE:AE,ROW()-COUNT_WW,1),IF(ROW()-(3+COUNT_WW+COUNT_AEAE)<=COUNT_AMAM,INDEX(AM:AM,ROW()-(COUNT_WW+COUNT_AEAE),1),"")))

And reprocessed these dates into one sorted, unique list. (In retrospect, I could have merged the dates first and then sorted (but hindsight is 20/20)

To have the dates chart correctly I had to write a macro to move only the date data (w/out formula) to a new column:

Private Sub Worksheet_Change(ByVal Target As Range)
    Sheet2.UpdateVal
End Sub

and

Sub UpdateVal()
    For i = 1 To 1000
        If Len(ActiveWorkbook.Sheets("Sheet3").Range("AV" & Trim(Str(i))).Value) > 0 Then
            ActiveWorkbook.Sheets("Sheet3").Range("AW" & Trim(Str(i))).Value = ActiveWorkbook.Sheets("Sheet3").Range("AV" & Trim(Str(i))).Value
        Else
            ActiveWorkbook.Sheets("Sheet3").Range("AW" & Trim(Str(i))).Value = Null
        End If
    Next
End Sub

Printer hours where pulled and summed from both the Proof and the Printer column:

=IF(AV4<>"",SUMIF($J$4:$J$100,AV4, $K$4:$K$100) + SUMIF($L$4:$L$100,AV4, $M$4:$M$100),"")

Bindery hours were pulled and summed:

=IF(AV4<>"",SUMIF($N$4:$N$100,AV4, $O$4:$O$100),"")

From the 3 final columns I defined names for each and used them to build the chart.

The chart is dyanamic in two ways. IF addtional date data is added (new rows) or dates are changed, the chart is redrawn with this date data. and secondly if the hour values are changed they are resummed and displayed automatically in the hours data.