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.

 

[shelby55]

Hi Skip

You are correct: one row of data is one data point. But I'm still not sure how to reference the target and baseline data....your thoughts?

Thanks.

 

[SkipVought]

select in your chart and right click. Choose Select Data. ADD a series and in the VALUES select the sheet/range of the data points for your Target and then do the same for the Baseline.

I like using Named Ranges. If you do, and assuming in these two cases, your Range Names are Target & Baseline, then your reference in the Chart Data Source, would be something like
[tt]
=SheetName!Target

and

=SheetName!Baseline
[/tt]


Skip,

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

 

[shelby55]

Hi Skip

I do like to use named ranges but this won't work because if the entire worksheet is showing (no filter) then Medicine baselines and targets would be on worksheet "Targets" at A2 to E2, Surgery would be A3 to E3 etc. When the filter is applied, Surgery is still A3 to E3 because Medicine is hidden. For that reason I can't use a formula for the fields because the row will depend on what department is selected. Note also that I don't believe I can use hard coded fields because the data will be changing as new months are available.

Or am I missing something?

 

[SkipVought]

but this won't work because if the entire worksheet is showing (no filter) then Medicine baselines and targets would be on worksheet "Targets" at A2 to E2, Surgery would be A3 to E3 etc

I do not understand.

When a dept is filtered on both sheets, for instance SURGERY, does it not show the data points that you need displayed from both sheets??



Skip,

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

 

[shelby55]

Hi Skip

Yes it does show only those datapoints BUT if I were to select = Targets!B2 then that would only be a valid selection for Medicine because that is what shows up as B2. Filters only make the other rows hidden so if Surgery is on line B4 then it is still B4 when the filter is applied, it doesn't become B2 because the other rows aren't removed, only hidden.

 

[SkipVought]

Of course the rows are hidden. THAT's the objective here, that only VISIBLE rows are PLOTTED.

Why would you "select = Targets!B2"???






Skip,

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

 

[SkipVought]

Let me make it clear, using your example...
[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]
USING Named Ranges...
In the CHART Source Data the
X-Axis YourSheetName!MonthEnding
Then on the Y-Axis
Series 1 YourSheetName!Sick_Hrs

or if you have a calculated column Headed SickPct

Series 1 YourSheetName!SickPCT
Series 2 TARGETSheetName!Target
Series 3 TARGETSheetName!Baseline

When you FILTER on Medicine, then you only get those data points displayed on the chart.

When you FILTER on Surgery, then you only get those data points displayed on the chart.






Skip,

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

 

[shelby55]

Hi Skip

I did that because I thought that is what you wanted. Note that the data from the Targets worksheet are as follows:
[tt]Department Sick_Trgt Sick_Bsln
Medicine 2.5 3.0
Surgery 3.0 3.5
[/tt]

So there is only one cell to plot as it's not by month...don't I need to get it showing up into each row of data on the Finance_Raw side so it will plot per month? What am I missing here?

 

[SkipVought]

You need a data point for each month. Thats whats on the x-axis...
[tt]
Selected Dept: Medicine
4.0|
|
3.5|
|
3.0|
|
2.5| * * * * * * * Sick Target
|
2.0|
|
1.5|
|
1.0|
|
.5|
|
0.0+---------------------------------------------
Mar Apr May Jun Jul Aug Sep
[/tt]


Skip,

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

 

[shelby55]

Hi Skip

You lost me....the Targets worksheet only has one cell per department for target and one cell per department for baseline. It's not department and month, only department.

So I chose the source of data for the sick time results as:
=Finance_Raw!$O$90:$O$107 (which is the cell where I have the % sick time calculated using C2/I2)

The Named Range I created was on the entire column for targets (without filters) so that whatever cell showed up based on the filter it would be the baseline but it's not accepting named ranges in the data of the chart. If I select only the cell Targets!B$7 then it only shows one data point because the Target is only one data point.

Not sure if this is the time to ask, but how do I create a filter for the Month Ending. I tried using the department filter but it's not working:

Sub Month_Filter()
   With Sheets("Finance_Raw")
            .AutoFilterMode = False
            .Range("A1").AutoFilter
            .Range("A1").AutoFilter Field:=2, Criteria1:=">"Sheets("Indi_Report").[F5]
   End With
End Sub

but it won't compile.

Thanks.

 

[shelby55]

Hi

Okay because I wasn't getting the part about the baseline and targets I just built those values into the Access query so that they appear on each of the Department's end months so that I can graph them.

Now my only thing is to filter the graph data based on the selected "month ending" as it is selected on the report worksheet (the same worksheet as where the Department is selected).

I was trying to model this after another type report but it only had one date of data per line whereas this has many depending on the department. What the other report did was have a worksheet for "graph data" and allowed for 10 dates of data. For the final row of data "allowed" (A29) it had a vlookup for the user selected date and the formula for A28 was A29-1. However, for this example it is by date so subtracting 1 is fine but my data is an entire month so not sure how to do that. Then each column was based on sumproduct for where the selected cell equaled the date in question.

I want only 6 months of data so if the month ending is June 2010 then I want only Jan to Jun 2010 showing on the graph.

Any help greatly appreciated - thanks.

 

[SkipVought]

Not sure if this is the time to ask, but how do I create a filter for the Month Ending.

Turn on your macro recorder and record setting th autofilter to meet your criteria.

I'll be back later for the other issues.

Skip,

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

 

[shelby55]

Hi Skip

I've been working on this since and I found out how to do the months...sort of.

I created a new filter:

Sub Date_Filter()
   With Sheets("Finance_Raw")
            .AutoFilterMode = False
            .Range("B1").AutoFilter
            .Range("B1").AutoFilter Field:=2, Criteria1:="<=" & Sheets("Indi_Report").[F5]
            
   End With

Then I added a worksheet change event to the same worksheet and got a compile error of "Ambiguous name detected: Worksheet_Change" so I found out that it because you can't have two events of the same name so I'm trying to combine them but can't seem to get the syntax right...now the department filter isn't correct.

To test that my date_filter is working I removed the departmental one and the date one is working using:

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

Now I just have to combine them...but can't seem to find the syntax to do so.

Thanks for hanging in there Skip!

 

[SkipVought]

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

  If Not Intersect(Target, Range("F5")) Is Nothing Then
      Date_Filter
  End If
  
End Sub

On your date filter, don't you want a lower limit? You have to pick TWO criteria. Figure out how to do that and then record it again.

Skip,

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

 

[shelby55]

Hi Skip

You are correct about a lower limit but I was hoping to create a graph worksheet which will allow me to use the named range of "Ending Date" (which is from the data validation on the Indi Report worksheet cell F5 for Month Ending) and then include only 5 other months. But I don't know how to reference the rest of the month array when the "ending date" is known.

Do you have any ideas on this?

 

[shelby55]

Hi Skip

Sorry but your filter code isn't working. Only the departmental is working but not the dates.

Thanks again for your assistance and patience on this...you are a gem.

 

[shelby55]

Hi Skip

Sorry but I lied: the filters are working but not at the same time i.e. if I select just the departmental change, it works. If I select just the date changes then it works but not if I change both at the same time.

There could also be an issue with the data updating....it's like I have to save the worksheet to get some of the formulae to change.

 

[SkipVought]

but not if I change both at the same time.

How does THAT work?

The INSTANT that you change either dept or date cell, the respective filter is changed. So NEITHER change at the same time!

You have some other behavior going on it seems.

Skip,

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

 

[shelby55]

Hi

Sorry but what I meant to say was that it only changes the last item changed so if in one session I am on the indi_report page and change first the department and then the month_ending drop down lists (from data validation) then only the month_ending column in finance_raw will be filtered correctly; if I select month_ending and then department, then only department filters correctly and all months of data show up.

You are correct that I can't do them both at the same time but what I mean is that it won't accept BOTH the department I pick AND the months that are <=month_ending selection.

How can I get them to both be applied at the same time? I'm not sure if this is the issue but I came across this article though I don't understand the workaround:

http://groups.google.com/group/micr...read/thread/c56eecb67b1a2cb6/51c07358ae7be82a

Thanks Skip.

 

[SkipVought]

OK. Macro record clearing all filters. Add that code to the dept filter, so here would be the result...

Select Dept:
All filters cleared
Dept criteria assigned

Select Date:
Date criteria assigned

Skip,

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