OzGrid Excel and VBA Newsletter September 2007

Excel Newsletter Index

EXCEL TIPS AND TRICKS | EXCEL VBA TIPS AND TRICKS

Here are some excellent ways to enhance your Excel skills and/or VBA skills with training and/or software

EXCEL TIPS AND TRICKS

Dynamic Reporting Of an Excel Table. Count, Sum, Average etc

Got any Excel Questions? Free Excel Help. Check out theseData Management Add-ins For Excel

Download Working Example

PivotTables are an excellent tool to use in Excel when you need a report, or statistics based on a table of data. However, for most users there are over-whelming and give too much detail.

Alternative Report

The Database Functions is Excel combined withData Validation and some outside the box thinking, is another easy way to get reports on your table data. We use Data Validation to refer to aNamed Range list ofOperators like =, >, >=, <, <=

Ok, lets name our table of data, including headings, Table on a sheet named Report. Next add new sheet and name it Criteria. In A1 of this new sheet Enter: =Report!A9 (Where A9 is the left most heading of your table) and in B1 Enter: =A1. In C1 of new sheet Enter: =Report!B9 (Where B9 is the 2nd heading of your table) and in D1 Enter: =C1. Follow this patter for all your table headings so you result in 2 copies of each heading.

In A2 Enter: =IF(Report!$E$3=A1,Report!$A$3&Report!$B$3,"") and in B2 Enter: =IF(AND(Report!$E$3=B1,Report!$C$3<>1),Report!$C$3&Report!$D$3,""). Now select A2:B2 and drag via theFill Handle to have the formulae under each duplicate heading. In any cell Enter: =IF(OR(Report!$C$3="",Report!$D$3=""),0,1) and name this cell ColCount. Next create a list of Operators like =, >, >=, <, <= in 1 Column. Name this range Operators. Finally Enter: =ADDRESS(1,MATCH(Field,A1:J1,0),,,"Criteria") & ":" & ADDRESS(2,MATCH(Field,$A$1:$J$1,0)+ColCount) in a cell and Name this cell CriteriaCell. This sheet can be hidden once you have it set-up and working.

Activate the Data sheet and starting in A2 Enter Operator 1, Criteria 1, 2nd Operator Optional, Criteria 2 Optional, Column Where E2 ends the list with text "Column". Select E3 and Name it Field. Select A2, hold down Ctrl and select C2. Go to Data>Validation and choose List from the Allow: box and in the Source: box add: =Operators. Select E2 and go to Data>Validation choose List from the Allow: box and in the Source: box add: =$A$9:$E$9 where this range represents your table headings. Now starting in A5 Enter SUM, Number COUNT, All Count, Product, Min, Max, Average across to G5.

Now the formulae going across, starting in E6, directly underneath their headings Enter;

=DSUM(Table,Field,INDIRECT(CriteriaCell))
=DCOUNT(Table,Field,INDIRECT(CriteriaCell))
=DCOUNTA(Table,Field,INDIRECT(CriteriaCell))
=DPRODUCT(Table,Field,INDIRECT(CriteriaCell))
=DMIN(Table,Field,INDIRECT(CriteriaCell))
=DMAX(Table,Field,INDIRECT(CriteriaCell))
=DAVERAGE(Table,Field,INDIRECT(CriteriaCell))

Select C3 (under the heading 2nd Operator Optional) go to Format>Conditional Formatting and use Formula is: =AND($C$3="",$D$3<>"") and set the format of the Background to Red. Finally, Select D3 (under the headingCriteria 2 Optional) Formula is: =AND($C$3<>"",$D$3="") and set the format of the Background to Red. This will let us know when you have used a second criteria without a second operator, or vice versa.

Download Working Example

EXCEL VBA TIPS AND TRICKS

Add Worksheets to Excel in Day Sequence/Order

There are occasions where adding a new Worksheet to an Excel Workbook should be added in a Weekday sequence. For example, you may need to add Worksheets, name them in a day order (Monday to Sunday) and have the Worksheets in the correct Weekday position. The code below will do this

Sub AddWsInWeekDayOrder()    Dim ws As Worksheet, wsTest As Worksheet    Dim lMatch As Long, strTest As String         ''''''''''''''''''''''''''''''''''''''''''''''''    ''''''''''Add Worksheets in Weekday Order'''''''''''''    ''''''''''Dave Hawley www.ozgrid.com''''''''''''    ''''''''''''''''''''''''''''''''''''''''''''''''    On Error Resume Next    Application.EnableEvents = False         For Each ws In Worksheets        lMatch = WorksheetFunction.Match(ws.Name, _         Array("MONDAY", "TUESDAY", "WEDNESDAY", "THURSDAY", "FRIDAY", "SATURDAY"), 0)        strTest = Choose(lMatch, "TUESDAY", "WEDNESDAY", "THURSDAY", "FRIDAY", _        "SATURDAY", "SUNDAY")        If lMatch <> 0 Then            Set wsTest = Nothing            Set wsTest = Sheets(strTest)            If wsTest Is Nothing Then                Sheets.Add After:=Sheets(ws.Index)                ActiveSheet.Name = strTest                Application.EnableEvents = True                Exit Sub            End If        End If    Next ws    Sheets.Add().Name = "MONDAY"    Application.EnableEvents = True    On Error GoTo 0End Sub

Got any Questions? Free Excel Help

Complete Excel Training Course Special!
We don't teach Excel from a manual, we teach Excel from experience!

Free Help Forum, not just Excel!

Instant Download and Money Back Guarantee on Most Software

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft

FREE Excel Help