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]

[tt]
G4: =IF(date(year(f4), month(f4)-5, Day(f4))<MIN(YourDateRange),MIN(YourDateRange),date(year(f4), month(f4)-5, Day(f4)))
[/tt]
If [lower date criteria calculation] is Less Than the MIN [Date in your range] Then MIN [Date in your range] Else [lower date criteria calculation]

Skip,

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

 

[shelby55]

HI Skip

Okay I understand that this formula will create the MIN field but how do I copy this field down to fill in the "in between" months.

For instance if I choose Sep 2010 as the month_ending the max month will be Sep 2010 and the min month will be Apr 2010....but how to calculate for May, Jun, Jul and Aug 2010 so that I can then use that for the sumproduct calculation?

Plus Skip you still haven't advised what worksheet you are referring to for the cell of G4. I really appreciate your help but I'm not understanding your directions.

 

[SkipVought]

how do I copy this field down to fill in the "in between" months

You DON'T.

This is the value for the Lower Limit of your Date Filter criteia.

This is the value for the lower limit of your SUMPRODUCT formula.

As I posted in the other thread, the sheet where you are selecting Dept (E4) and Date for the upper limit (F4), also has....

The LOWER LIMIT (G4)!!!

Skip,

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

 

[shelby55]

HI

Okay so I thought I would try to apply the min filter so that I could have the finance_raw data filter correctly showing only the 6 months of data and use that to graph.

I edited the filter sub with:

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"), _
           Operator:=xlAnd, Criteria1:=">=" & Sheets("Indi_Report").Range("F6")
         
        End If
    
    End With

End Sub

This doesn't work to only show the Min even though it is working in F6. Note also that it won't let me use:

If Sheets("Indi_Report").Range("F5:F6") <> "" Then
----------

as I get a type mismatch error message.

I added F6 to the code on the Indi_Report worksheet:

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

but it's not working to filter the data accordingly.

Can you please advise what is wrong with the code? Will I be able to graph from the raw_data sheet? Thanks.

 

[SkipVought]

This doesn't work to only show the Min...

Of course it does not ONLY show the Min!!!

It filters LESS THAN OR EQUAL TO THE F5 value and GREATER THAN OR EQUAL TO THE F6 value. It's a RANGE OF VALUES.

The ONLY case where only one month is displayed, is when the MIN dat value is selected by the user!

Skip,

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

 

[shelby55]

Hi

I'm not sure why I'm not getting my point across but where did you read that I wanted a single value because I don't. I created the code above to get the raw_data to filter the range where month_ending was <= max value (F5) and >= min value (F6). Then I was hoping to graph on those 6 months right from the raw_data worksheet.

As stated, the above doesn't work to give me the range, it gives me the max value but the minimum is always Apr 2009 (so more than 6 months of data).

Can you please advise why the above isn't working...thanks.

 

[SkipVought]

Please post the formula calculating the lower limit, in F6, I believe.

Skip,

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

 

[shelby55]

Hi

=IF(DATE(YEAR(DateSelected), MONTH(DateSelected)-5, DAY(DateSelected))<MIN(MyDates),MIN(MyDates),DATE(YEAR(DateSelected), MONTH(DateSelected)-5, DAY(DateSelected)))

DateSelected is the named range for the selected "month ending" date in F5. The resulting value in the F6 based on this formula is correctly showing the min value.

Thanks.

 

[SkipVought]

The resulting value in the F6 based on this formula is correctly showing the min value.

So if that value is correct, why is the filter not reflecting this value, as it should?

Skip,

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

 

[shelby55]

Hi

I don't know but I changed my code to:

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
        If Sheets("Indi_Report").Range("F6") <> "" Then
                    .AutoFilter Field:=2, Criteria1:=">=" & Sheets("Indi_Report").Range("F6")
        End If
    End With
End Sub

and now the filter is working as I wanted. Now I hope that I can just graph from the raw_worksheet.

Thanks.

 

[shelby55]

HI

I spoke too soon, the filter isn't working. If I select max of March 31, 2010 then the minimum value should be October 31, 2009. These values are represented in F5 and F6.

But the raw_worksheet shows data with a minimum of October 31, 2009 (correct) but a maximum of Sep 30, 2010. So now the opposite is happening...the max is always Sep 30 2010 whereas before it was the min was always the min of the entire database: Apr 2009.

If you could just explain why my code isn't working I'd appreciate it - thanks.

 

[SkipVought]

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

        If Sheets("Indi_Report").Range("F5") <> ""  Then
           .AutoFilter Field:=2, Criteria1:="<=" & Sheets("Indi_Report").Range("F5")[b][red], Operator:=xlAnd, Criteria2:[/red][/b]=">=" & Sheets("Indi_Report").Range("F6")
        End If

YOUR code is only setting ONE CRITERIA for date!!!!!

Skip,

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

 

[shelby55]

HI

The code you are quoting isn't from my latest attempt. The one above your post is so it is looking for a min and max...or so I think.

I don't understand why it is so hard for you to just advise me of what I'm doing wrong...your answers are often cryptic and I have no idea what I should be doing. I am not an expert, clearly, but this is a helping forum, not just for people with advanced skills.

 

[SkipVought]

this code sets ONE DATE CRITERIA...

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
        If Sheets("Indi_Report").Range("F6") <> "" Then
                    .AutoFilter Field:=2, Criteria1:=">=" & Sheets("Indi_Report").Range("F6")
        End If
    End With
End Sub

You need to replace the two if statements containing 2 different assignments for ONE criteria, with my code that assigns TWO criteria for date!

Skip,

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

 

[shelby55]

Hi

Your code at 22:44 is almost the same as I posted at 21:40 with the exception of identifying the second criteria as criteria2.

You couldn't tell me I was close? I know you like people to learn on their own but I found how to combine two criteria on my own and you couldn't tell me I was just slightly off?

 

[SkipVought]

I am not an expert, clearly, but this is a helping forum, not just for people with advanced skills.

I'm attempting to lead you thru a thought process and the steps needed for you understand how to fish.

Skip,

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

 

[SkipVought]

You had only assigned ONE criteria, after I specifically stated TWO criteria in a number of replys.

If you did not know how to assign TWO criteria, you could have asked. I'm glad that you figured it out on your own, good for you, but I don't know what you know or not, about Excel.

I hope your filter is working as required and that your chart is reflecting the information you need.

This technique of filtering data that a chart is pointing to, is a powerful interactive technique that you can now use time and again, with a minimum of VBA coding: a FILTER procedure and a WORKSHEET CHANGE EVENT procedure.

Good job and good luck!

Skip,

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

 

[shelby55]

Hi Skip

Thanks for all your assistance on this...many people would have given up many posts ago.

I picked up on the two criteria hint but didn't know where to apply it...it just would have been nice to know that I had the original one with correct syntax EXCEPT for not selecting on two criteria as it was literally a one character change (yet oh so important).

Yes I will certainly use this approach in future...thanks again.

 

[SkipVought]

it was literally a one character change

Not So!

You must ALSO have the AND operator in your code, setting BOTH criteria: Criteria1 AND Criteria2.

Changing ONLY the last If criteria from Criteria1 to Criteria2 will NOT work as expected. This is why it was crutial for you to have recorded setting BOTH criteria in the AutoFilter, at the same time, using AND.

Skip,

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

 

[shelby55]

Hi Skip

I know but my original post at 21:40 on Nov 2 HAD the AND as well. The only flaw in my code was that I had <=criteria1 and >=criteria1 instead of identifying the second criteria as criteria2.

So I was very close...anyway, all fixed now so thanks.