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

I ended up trying to select items because there didn't seem to be any code for the deletion:

Sub turnoff()

    Sheets("Finance_Raw").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=1, Criteria1:="Medicine"
    Selection.AutoFilter Field:=2, Criteria1:="11/30/2009"
    Selection.AutoFilter
End Sub

So I'm not sure what to do next.

 

[SkipVought]

Data > filter > Show All


Skip,

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

 

[shelby55]

Hi

Thanks Skip.

Okay then I get the code of:

ActiveSheet.ShowAllData

Just using .showalldata doesn't work within the current code so not sure where to put it.

 

[SkipVought]

Sub Dept_Filter()
  With Sheet2
     .ShowAllData

     .AutoFilterMode = False
     .Range("A1:D1").AutoFilter
     .Range("A1:D1").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

Thanks very much Skip but that also didn't work. I did find a solution though:

Sub All_Filt()

   With Sheets("Finance_Raw").Range("A1").CurrentRegion
        .AutoFilter
        If Sheets("Indi_Report").Range("F4") <> "" Then
            .AutoFilter Field:=1, Criteria1:=Sheets("Indi_Report").Range("F4")
        End If
                
        If Sheets("Indi_Report").Range("F5") <> "" Then
            .AutoFilter Field:=2, Criteria1:="<=" & Sheets("Indi_Report").Range("F5")
        End If
    End With

Then for the worksheet itself:

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

Now I just need to know how to limit the selected timeframes to 6 data points. So even though I choose September 2010 as the ending date (which means that Apr 2009 to Sept 2010 will be visible on the Finance_Raw worksheet), I only want Apr to Sep 2010 on the graph.

In another example of this type of report that I have they use the range top, range size and range bottom approach and then use sumproduct to extract the data for the particular month ending.

I have the named range (spec_date) for the date selected from Indi_Report so if I put it in the 6th spot of a new worksheet for graph data, I don't know how to get the other months showing i.e. spec_date minus 1 is only subtracting a day from the specified month, not giving me the next month in the array.

Are you able to help with that? Thanks.

 

[SkipVought]

In your date filter you need TWO criteria; One is less than or equal to and the other is greater than or equal to.

Record setting those two criteria and post back with your recorded code.

Skip,

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

 

[shelby55]

Hi Skip

I was hoping that I could determine the second criteria by creating another worksheet which dynamically shows the data to graph by setting the range top, range size and range bottom.

The example that I have is only for one criteria of data and has the "raw" data showing daily information. The end point for the dates to be shown is determined by a vlookup to the raw data based on the user selected date. Then every entry after that is, for example, A29-1. But subtracting 1 from the selected date in my example gives me a non-existent month ending of one day less than the end of the month.

top range = {Match(1,--(B9:B29<>""),0)}
range size=count(B9:B29)
range bottom =Match(9.999999999E+307,B9:B29)

Is there anyway that I can do something like this with the data that I have? Thanks.

 

[SkipVought]

In the adjacent cell to your selected date (f4)
[tt]
G4: =date(year(f4), month(f4)-6, Day(f4))
[/tt]
There's your lower criteria.

Skip,

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

 

[shelby55]

Hi Skip

You are one very smart dude!!! I took what you gave me and actually applied it for the other cells on the graph worksheet so the next month is G4: =date(year(f4), month(f4)-4, Day(f4)) etc.

Now hopefully I can use the vlookup for values in the finance_raw table where they match the date ending in the cells you've just helped me create. Then my goal is to name the ranges in the graph_worksheet to use them in the graphs.

I may still have questions but this was a HUGE revelation...thanks so much for your tireless assistance, Skip. I wish they allowed more than one star per person per post because I would send you a huge bagful!!!

 

[shelby55]

Hi Skip

Sorry to be a pain but I don't know how to reference the cell from Finance_Raw based on the month showing up in Finance_Graphs A9 to A14.

Can you get me started? Thanks.

 

[shelby55]

Hi Skip

Sorry but I've encountered a second problem: for the formulae you showed me that works great but it is going to pick a month whether that month has data or not. So if my month ending is Jun 2009 then it should only show Apr to Jun 2009 data since it only goes back to Apr 2009. But it currently shows Jan 2009 as the minimum value which can't be. How do I refer to the minimum entry in the mydates array so that nothing will show up if there aren't values for it in the data?

Thanks.

 

[shelby55]

Hi Skip

Still having troubles with this....note that it appears that it doesn't matter if I have a filter or not, because any reference to cells still will reference cells not showing.

Does that make sense to you?

 

[SkipVought]

regarding your lower date criteria, use an IF function to set the the lower limit, when the selected dat reaches the limit.

regarding your ranges, the ENTIRE column of data is a Named Range, NOTHING in that column, visivle or not, need to be refernce in order to use this charting technique.

The Filters display all the relevant data. The chart plots only visible data.

If your chart is not performing this way, there is a problem in your desig\n.

Skip,

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

 

[shelby55]

Hi Skip

Thanks for responding.

Not sure what you mean about the lower limit...what do I reference in the if statement for the lower limit i.e. the dates have a named range of TimePeriods, how do I reference them to indicate if not within TimePeriods then don't use.

As stated previously, I want to only show 6 months of data on the graph and the filter is for month ending so if someone chooses Sept 2010 as month ending I only want Apr to Sep 2010 showing up but the fitler will allow all 18 months to be visible and potentially graphed....that is my problem.

Thanks.

 

[SkipVought]

well the forumla is as I posted, IF the selected date is not less than the MIN date in your DATE column+5 months.

If the selected date is less than that value, the lower date is the MIN date in your Date column.
[tt]
=IF(date(year(f4), month(f4)-5, Day(f4))<MIN(YourDateRange),MIN(YourDateRange),date(year(f4), month(f4)-5, Day(f4)))
[/tt]
I do not understand what you mean by "named range of TimePeriods". You ONLY need ONE Date range! Seems to me that you're making this much too complicated!

Skip,

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

 

[shelby55]

Hi Skip

You're probably right about making it too complicated but even if the filter did work, depending on the month_ending, the graph would show more than 6 months of data.

So I'm trying to create a worksheet that will show the 6 months of data to graph based on selection.

So on the "graph_data" worksheet if Sept 2010 was selected as the "month ending" month, I would have:
[tt]Month %Sick %Sick_Bsln %Sick_Trgt
4/30/2010 3.3 2.8 2.5
5/31/2010 2.5 2.8 2.5
6/30/2010 2.8 2.8 2.5
7/31/2010 3.6 2.8 2.5
8/31/2010 3.7 2.8 2.5
9/30/2010 1.8 2.8 2.5[/tt]

The values for %sick, %Sick_bsln and %Sick_Trgt all come from the finance_raw worksheet columns W, X and Y and since the filter seems to be ignored for index cases it will need to be based on month_ending selected and department_selected which is selected by the user from the indi_report worksheet, cells F5 and F4.

So what I was doing was where the 9/30/2010 from above is showing I would put the "dateselected" named range there and try to build backwards using:

=DATE(YEAR(DateSelected),MONTH(DateSelected)-1,DAY(DateSelected))

for the next one above and so on until I got my 6 months. My comments before were related to this code above allowing for months that there isn't data for. For instance, if the user selects Jun 2009 then because the database only has Apr 2009 to Sep 2010 data the above should be
[tt]Month %Sick %Sick_Bsln %Sick_Trgt



4/30/2010 3.3 2.8 2.5
5/31/2010 2.5 2.8 2.5
6/30/2010 2.8 2.8 2.5 [/tt]

Once this graph_worksheet is pulling the correct data, then I will created named ranges for each column and then build the graph using manually created series based on the named ranges. Then the graphs will change dynamically based on user selection.

I look forward to your comments/advice.

 

[SkipVought]

There is no need to do that. There is a mathematical relationship between the selected date and the MIN(MyDate) value. There are any mumber of ways to CALCULATE a lower cirteria limit that will never, " show more than 6 months of data" or ANY date less than the MIN(MyDate).

I gave you one.

I do not see how your filter would not be able to work as specified.

As a matter of good Excel practice, formulas should not be taylored to individual cases, like...

=DATE(YEAR(DateSelected),MONTH(DateSelected)-[red]1[/red],DAY(DateSelected))

but rather coded to handle ANY CASE!

Skip,

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

 

[shelby55]

Hi Skip

Okay but how do I use min and max to extract the data that I need to graph? I got your formula to work for the minimum and I know maximum is the dateselected.

Or are you saying apply the minimum value known to the filter so that I'm only graphing from the finance_raw worksheet?

 

[SkipVought]

I am NOT saying, "apply the minimum value known to the filter"

I am saying that you can use the MIN(MyDate) value in the formula in G4, the lower criteria, to limit that value. I poestd an example.

Skip,

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

 

[shelby55]

Hi Skip

I'm sorry but I'm not understanding you...I have the min value thanks to your formula and I have the max based on the dateselected but how do I translate this to get the months in between to then use those cells with the sumproduct formula you provided earlier to extract the data I need for graphing?

I'm just not understanding what you want me to do with the min...am I to graph right from the finance_raw worksheet and scrap the graph_data worksheet?