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]

hi,

You can query the access database directly.

You can query either the access database or one of your sheets, to get Select Distinct to return a list of unique values as the source for a combobox.

You can make the combobox as an in-cell dropdown/data validation list. A CHANGE in the cell value can be namde to trigger a parameter query to return a resultset based on the criteria in your combo/cell.

Skip,

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

 

[xlbo]

Charts can function on visible cells only
therefore you can link your department dropdown to an autofilter on the "RAW Data" sheet and filter teh data based on dept - chart will then show data just for that dept

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Yes, why didn't I think of that?

Skip,

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

 

[shelby55]

Hi

Thanks Geoff and Skip! Does the dropdown filter need to be on the raw sheet as I want that hidden from the user and only using the Scorecard page.

I want to be able to filter by department but also timeframe so I was thinking of a data validation data element citing the raw data as its source. Then I would make the results a "named range" for referencing in formulae.

 

[xlbo]

Skip - dunno - that's one I got from you!!
shelby - you can use some code behind the scenes to link the selection from the dropdown on your dashboard sheet to the autofilter on the RAW data sheet

If this is an approach that you'd be happy with I would suggest posting in the VBA forum. As a starter for 10, here is some code that I used to do exactly the same as you are doing - 3 drop downs with 2 different types of info - not all combinations were valid so needed to have a check to make sure the charts didn't change unless the view was "valid" - just hooked into the worksheet "Change" event and set teh autofilter values on the data sheet which the charts read off

Function OpsChartView(l_Hier_Val As Long, str_Hier_Type As String, str_View_Type)
With sht_TrendDB
    .[dr_Summary_Trending].AutoFilter Field:=4, Criteria1:=str_Hier_Type
    .[dr_Summary_Trending].AutoFilter Field:=3, Criteria1:=l_Hier_Val
    .[dr_Summary_Trending].AutoFilter Field:=5, Criteria1:=str_View_Type
End With
End Function


Private Sub Worksheet_Change(ByVal Target As Range)
Dim iSect As Range
Dim blnValidView As Boolean
Dim strChosenHier As String
Dim lHierVal As Long
Dim strViewType As String

Set iSect = Intersect(Target, [sr_View_change])

If iSect Is Nothing Then Exit Sub

blnValidView = True

If [sr_Sel_Area] <> "" Then

    strChosenHier = "Area"
    lHierVal = [sr_Sel_Area]
    strViewType = [sr_ViewType]
    blnValidView = True
    
ElseIf [sr_Sel_Terr] <> "" Then

    strChosenHier = "Territory"
    lHierVal = [sr_Sel_Terr]
    strViewType = [sr_ViewType]
    blnValidView = True
    
ElseIf [sr_Sel_Reg] <> "" Then

    strChosenHier = "Region"
    lHierVal = [sr_Sel_Reg]
    strViewType = [sr_ViewType]
    blnValidView = True
    
Else

    blnValidView = False
    
End If

If [sr_ViewType].Text <> 0 Then

    blnValidView = True
    
Else

    blnValidView = False
    
End If


If blnValidView Then

    Call OpsChartView(lHierVal, strChosenHier, strViewType)

End If

End Sub

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[shelby55]

Hi

Thanks XLBO...I will migrate this to VBA for another post but I'm still trying to figure out format.

Will it be possible to show two different timeframes. For instance, if I have Apr to Sep 2010 data and I want to also reference Apr to Sep 2009 data, will that be possible? I also want to be able to filter for the timeframe based on what the user picks..for instance, I'm only showing the last 4 months of data due to size of the scorecard and selection will be on the end month. So if they pick an end month of June 2010 then they will get March to Jun 2010.

Thanks.

 

[SkipVought]

Have a series for 2010 data and a different series for the 2009 data? Source data summarized like...
[tt]
Yr Mo [2010 Values] [2009 Values]
...
[/tt]
filter on Mo to get whatever month span you want. you would plot the data in columns B.



Skip,

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

 

[shelby55]

Hi Skip

I love that suggestion....I'll try that!! I'm sure this forum will be hearing from me again about this latest creation!

 

[shelby55]

Hi

Okay, I have raw data for this on another worksheet which has each department and then each month (I haven't worked Skip's idea in yet...right now they only want a template). On the graph page I have the data validation using the department and another one for month ending.

To simplify the data "get" I think the worksheet with the values for the graphs should also be sorted with whatever department is selected on the graph page. I've never done that before - is that VBA(and I'll ask about it in the VBA forum) or another methodology?

Thanks.

 

[SkipVought]

You could populate the chart data sheet, using MS Query, or you could set up your chart data table with formulas. I've done it both ways, with a minimal amount of VBA.

Skip,

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

 

[shelby55]

Hi Skip

Thanks...but can you give me a hint on where to start?

I have the raw data in a worksheet of the same workbook. It is showing month and department and the data elements required. In this case there is a sick hours column and total hours so that I can divide one by the other for the % sick time which I will graph.

Thanks.

 

[SkipVought]

Well why couldn't xlbo's suggestion work?

Point each chart series to that sheet and the appropriate column. put your percent formula in an adjacent column.

Turn on your macro recorder to record setting the dept filter.

Create a unique list of depts and use a data validation dropdown for the list of depts. When that cell changes, fire the filter macro and your cahrt will display that dept's data.

Skip,

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

 

[shelby55]

Hi

Sorry to be a pain but I don't even know how to manually filter for a department from the drop down list to do it via the macro recorder. Note also that the graphs have to be able to change for whatever "end month" is chosen. Plus I don't know how to get the target and baseline values (which are single values per department on another worksheet) into the worksheet per month.

Any help greatly appreciated.

 

[shelby55]

Hi

Okay I thought I would describe what I've done thus far to see if anyone can help me finish this. I am using Excel and Access 2003.

What I want to do is have one graph based document changing based on the department selected but also the end month chosen. The graph will show 6 months of data and if the user wishes to query the previous 6 months then they can.

I have a query from Access populating a worksheet called Raw_Finance_DataFile. I have another query for months available which is the named range that populates the pull down list for end months. I have another worksheet with the list of Departments which is used in the pull down list for Departments. Both pull down lists are data validations using the lists as the source of information.

I also have another worksheet with calculations per department for the baseline and target per indicator which looks like this:
Department %Sick_Bsln %Sick_Trgt %OT_Bsln %OT_Trgt
Medicine 3.9 3.5 4.2 3.8
Surgery 4.2 3.8 2.7 2.4
Emergency 5.0 4.5 3.3 3.0

How can I reference these values specific to the column of data for the department?

Finally, I have a worksheet for use with the graphs that will only show the months requested. But for this sheet I'll need to be able to only show the data that is selected from the Graph sheet i.e. the month range and the department. How can I do this?

Thanks.

 

[SkipVought]

I don't even know how to manually filter for a department

FIRST the AutoFilter must be on. Select in your table and Data > Filter > AutoFilter. You should now have the AutoFilter on every column in the heading row of your table.

NOW turn on your macro recorder and record selecting a Dept.

TURN OFF the macro recorder.

Do the same thing for the DATE, only use a greater than or equal to criteria.

Post your recorded code.



Skip,

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

 

[shelby55]

Hi Skip

I did as you suggested and got:

Sub slp_filter()
    Selection.AutoFilter Field:=1, Criteria1:="Ambulatory Care"
End Sub

 

[SkipVought]

You want to change Selection to fully qualify the Sheet and top left cell in your table, assuming that your table befins in A1...

Sub slp_filter()
    With Sheets("Raw_Finance_DataFile").[A1].AutoFilter _
       Field:=1, _
       Criteria1:="Ambulatory Care"
    End with
End Sub

Then, where is your Dept Data Validation DropDown cell? Assuming that it's on Sheet ChartData, cell A1...

Sub slp_filter()
    With Sheets("Raw_Finance_DataFile").[A1].AutoFilter _
      Field:=1, _
      Criteria1:=Sheets("ChartData").[A1]
    End with
End Sub

Then in the ChartData Code Window (right click the sheet tab and select View Code), run the slp_filter procedure in the Worksheet_Change event

Private Sub Worksheet_Change(ByVal Target As Range)
   if not intersect(target, Range("A1")) is nothing then
      slp_filter
   end if
End Sub

NOW each time you select a value in the Data Validation Drop Down, the filter criteria changes and the table is filtered on the selected dept.

Then do a similar exersize for the date range

Skip,

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

 

[shelby55]

Hi Skip

Thanks so much. I used the following based on my worksheets and the cells required:

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

However, there seems to be objection to the Field:=1 part of the code and I get a compile error.

Finance_Raw is the name of the worksheet with the data to filter and it's column A that needs to be filtered; Indi_Reports is the worksheet where the drop down list for departments exists and it's located in F4.

Thanks.

 

[shelby55]

Hi Skip

Note also that I didn't understand your first box of code...I assume you were just showing me that is the code if I was only selecting Ambulatory Care but I didn't enter that code anywhere, only the code for matching the selection to the report page. Was that correct?