Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

Dynamic Excel Report

| | Information Helpful? Why Not Donate.

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

Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only ~~$145.00~~. $59.95 Instant Buy/Download, 30 Day Money Back Guarantee & Free Excel Help for LIFE!

Got any Excel Questions? Free Excel Help. Check out these Data 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 with Data 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 a Named Range list of Operators 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 the Fill 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 heading Criteria 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

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

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

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