Excel Report for Many Departments 3

[shelby55]

Hi

I am using Excel 2003 to develop an automated template. The data will come from queries in an Access database.

My plan is to have a page of the Excel worksheet designated for the raw data that will populate the graphs on the report page. The report page will be fashioned like a dashboard.

The reason for this post to ask about format. The data will be queried by month and department. Can the raw data be split out like this as well and the graph change according to what department is selected in a pull down list? How would you create such a thing for multi-departments? Thanks.

 

[SkipVought]

So sorry. I did not check my code as I was browsing from a laptop that did not have Excel...

Sub slp_filter()
    Sheets("Finance_Raw").[A1].AutoFilter _
      Field:=1, _
      Criteria1:=Sheets("Indi_Reports").[F4]
End Sub

Skip,

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

 

[shelby55]

Hi Skip

I did some more searching on this and found code that worked from

http://www.ozgrid.com:

Sub Dept_Filter()
With Sheet2
            .AutoFilterMode = False
            .Range("A1:D1").AutoFilter
            .Range("A1:D1").AutoFilter Field:=1, Criteria1:=Sheets("Indi_Report").[F4]

   End With
End Sub

I think that I need to apply it to the Targets and Baselines worksheet too so that only the values for the specific Department are pulled across. Now I'll attempt to run for timeframe and let you know how I do.

Thanks for giving me the start, Skip!!

 

[SkipVought]

I advise AGAINST defining a range like Range("A11"). You really only need ONE CELL; ideally the top left cell in the heading row. Excel figures out the table range.

Skip,

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

 

[shelby55]

Hi Skip

Okay, thanks for the heads up, I will heed your advice. My code isn't working right now anyway.

As per my last entry, I don't know what your first set of code was advising me to do. All I've done is, in module1:

Sub Dept_Filter()
With Sheet2
            .AutoFilterMode=false
            .Range("A1").AutoFilter
            .Range("A1").AutoFilter Field:=1, Criteria1:=Sheets("Indi_Report").[F4]
   End With
End Sub

Then in ThisWorksheet I have:

Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Intersect(Target, Range("A1")) Is Nothing Then
      Dept_Filter
   End If
End Sub

Should I have the second part on the worksheet it is pertaining to? Thanks.

 

[SkipVought]

since your dropdown is in F4...

Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Intersect(Target, Range("[b][red]F4[/red][/b]")) Is Nothing Then
      Dept_Filter
   End If
End Sub

Skip,

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

 

[shelby55]

Hi Skip

Thanks but still not working...do I need to advise what worksheet the target is in? Where does the code need to be for both pieces of code?

Thanks for your patience, Skip!

 

[SkipVought]

The worksheet change event is in the WORKSHEET code window where you are changing the F4 value in the dropdown data validiation.

The filter code needs to be in a MODULE.

Skip,

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

 

[shelby55]

Hi Skip

Ta da....it works. Thanks so much. I'm sure I'll have other questions but should I start a new thread?

 

[SkipVought]

Not if its related to thread68-1625833


Skip,

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

 

[shelby55]

Thanks Skip...sorry but still related!

I attempted to do the same as above so the filter would apply to another worksheet (the targets and baselines). I figured that if the targets should would also filter by department then the results would mean that only the remaining data would be applicable per department.

I first tried to add to the current filter:

Sub Dept_Filter()
With Sheets("Finance_Raw","Targets")
            .AutoFilterMode = False
            .Range("A1").AutoFilter
            .Range("A1").AutoFilter Field:=1, Criteria1:=Sheets("Indi_Report").[F4]
   End With
End Sub

But it wouldn't compile and had a problem with the above.  So do I have to create another filter?  Or how to reference multiple sheets? 

Thanks Skip!

 

[SkipVought]

Sub Dept_Filter()
   With Sheets("Finance_Raw")
            .AutoFilterMode = False
            .Range("A1").AutoFilter
            .Range("A1").AutoFilter Field:=1, Criteria1:=Sheets("Indi_Report").[F4]
   End With

   With Sheets("Targets")
            .AutoFilterMode = False
            .Range("A1").AutoFilter
            .Range("A1").AutoFilter Field:=1, Criteria1:=Sheets("Indi_Report").[F4]
   End With
End Sub

Skip,

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

 

[shelby55]

Hi Skip

Thank you so very much, works like a charm!!

 

[shelby55]

Hi

Sorry but I am so stupid....the worksheet filters as I'd like which should work out for the graphs but filtering only "hides" the other rows so I still can't get at the targets and baselines per department by simply pointing to the cell.

Can you think of how I could/should do this? Thanks.

 

[SkipVought]

Why do you have targets and baselines with your raw data? I'd put them in a separate table.

Skip,

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

 

[shelby55]

Hi

They are in a separate table but if I want to include them in the graphs then I'll need to be able to sort them out by department, correct? Plus I was trying to incorporate them into a worksheet for the graphs because the months showing for the graph will be based on month ending.

 

[SkipVought]

Seems to me, as we uncover the requirements, that you may not want to filter the raw data. Here's why. If your raw data needs to be aggregated in any way,for instance counted or summed by month, and if you need to display a baseline and target, then your direction ought to be in summarizing in some manner, on the ChartData sheet. That summarization could be a query or by formulas, as I originally posited.

Skip,

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

 

[shelby55]

Hi Skip

Okay great but I'm not sure how to do that. I don't understand xlbo's post but I'm assuming that is the route you think I should take?

The data is already summed because it is monthly and I need to graph the charts specific to the department selected on the Indi_Report page. I also wanted to limit the number of months shown to 6 and the user will select "end month" from the Indi_Report page as well so if they select September 2010 then Apr to Sep 2010 will be graphed.

So I was trying to create a Graph_Data worksheet to get at all these values for me including taking the numerators and denominators from the Finance_Raw sheet to make them into percentages.

Any help is greatly appreciated.

 

[SkipVought]

Please post some sample data from the first sheet that you want to plot.

Please post only the columns relevant to the chart.

Describe what additional data (series) you want to see on the chart that is not represented by the data in the posted table.

Describe how you want the chart to display the data.

Skip,

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

 

[shelby55]

Hi Skip

The data on Finance_Raw that comes from the Access database in a query is:

[tt]Department MonthEnding Sick_Hrs OT_Hrs Total_Hrs
Medicine 9/30/2010 124.29 193.92 1047.83
Medicine 8/31/2010 128.57 152.35 984.79
Surgery 9/30/2010 6.21 107.36 757.53
Surgery 8/31/2010 6.86 233.34 823.55
[/tt]

For each Department I actually have Apr 2009 to Sept 2010.

I wish to show 6 months of data based on the the end month selected so that if the user selects Sept 2010 then Apr to Sep 2010 will be displayed. I am going to have many graphs but for an example of one graph I would like to show % sick time (sick hours*100/total hours) per month.

On another sheet I have target sick % sick hours and baseline % sick time per Department and I would like those to values also represented on the same graph as above. These values will be the same for each time period as they don't vary monthly or yearly.

I want the graph to be a line graph with each series (% sick time, baseline and target) to have their own colour series.

Thanks Skip...I hope this is all the information you require to direct me.

 

[SkipVought]

So your data is already aggregated by year/month. ONE ROW of data per data point in your chart, correct? 6 Months of data: 6 data points in your chart, correct?

In that case, filtering will work perfectly well, including referencing the target and baselines on the other sheet, as long as the same filtering criteria is applied to both.

Skip,

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