Back to Excel Newsletter Archives
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
Build an Automated Stock Trading System |Build an Automated Sector Fund System |Build an Automated Spread Trading System |Technical Indicators Library Open Source |Code VB & VBA |Smart VBA |Print VBA |ALL TRAINING...
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.
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;
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
See Also Add Worksheets || Add Worksheets in Month Order || Add Sheets in Numeric Order || Sort Excel Sheets/Worksheets
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
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