All Excel Templates Normally an 80% saving at $299.00. Super Special $127.00! Best Value on the WWW More Hot Specials Here!
|Microsoft Excel Add-ins||Financial Add-ins and Software|
|Microsoft Excel Training & Tutoring||Conversion Software|
|Microsoft Excel Templates||Construction and Estimating Software|
|Excel, Word, Access Password Recovery||Financial Calculators|
|Corrupt Excel, Word, Access File Recovery||Business Planning|
|Excel & VBA books||Excel on the WWW|
|Windows & Internet Software||Downloadable Excel Training|
|Barcode, Fonts, ActiveX, labels and DLL's||OUR CURRENT SOFTWARE SPECIALS!|
|Chart Tools & Add-ins||The Analysis Add-ins Collection|
|Trading Software & Add-ins||TOTALLY FREE 24/7 EXCEL, VBA SUPPORT!|
We Recommend SmartDraw for Flowcharts
|SmartDraw is the quick and easy way to draw quality flowcharts, org charts, web graphics, and business presentations. You can try SmartDraw free for 30 days and see why it was voted "Best Business Program" two years in a row. For business charts and diagrams- ISO 9000 Flowcharts, Floor Plans, Circuit Diagrams, Flow Charts, Org Charts, VisualScript XML, Floor Plans, Business Forms, Network Diagrams, Circuit Diagrams, Engineering Diagrams, Flyers, Maps, Timelines, Clip Art, and Web Graphics and MUCH MORE|
ADD-IN CATEGORIES: Financial Excel Add-ins | Charting Add-ins | VBA Macro Add-ins |Data Management Add-ins | Construction and Estimating Add-ins |Excel Converters |Excel trading-add-ins
What's New at OzGrid | Excel Tips and Tricks | Excel VBA Tips and Tricks
Firstly we must wish all of our visitors a Safe and Happy New Year. We hope that you had a wonderful, restful Christmas and are all fired up and ready to face 2004.
We had a brief interlude over the Christmas and New Year period, but in the last week or so things Start ed getting busy again.
Over the Christmas period we changed servers for our web-site which went extremely smoothly until such time as we needed to try and upload the database for theQuestion Forum . This took a little longer than expected, and at one point it looked like we were going to loose the lot. Fortunately persistence paid off and all should now be back to normal.
Our book "100 Excel Hacks" is about two thirds of the way through, with us entering the extremely time-consuming "editing" stage. We hope that it will be wrapped up in the next few months, so we will keep you informed of a likely publication date in forthcoming newsletters.
Very large drop in ALL our training prices, these can be viewed here These price will remain until we have completed the book. About another month or so.
Barcode, Fonts, ActiveX, DLL's, Labels and more!
The SUBTOTAL Function in Excel is used to perform a specified function on a range of Auto Filtered cells. When the Auto Filter has been applied the SUBTOTAL function will only use the visible cells, all hidden rows are ignored. The operation performed is solely dependent on the number (between 1 and 11) that we supply to its first argument Function_num For example;=SUBTOTAL(1,A1:A100)
will Average all visible cell in the range A1:A100 after an Auto Filter has been applied. If all rows in A1:A100 are visible it will simply Average them all and give the same result as =AVERAGE(A1:A100)
The number for the first SUBTOTAL argument, Function_num, and it's corresponding function are as shown below
As we only need to use a number between 1 and 11 we can have the one SUBTOTAL function perform a function of our choice. We can even make the choice from a drop-down list which resides in any cell. Here is how;
Now all you need to do is select the required SUBTOTAL function from the Combo box and the correct result will be displayed.
Excel, Word, Access Password Recovery ||Corrupt Excel, Word, Access File Recovery
This month I thought we would look at how we can use Excel VBA to take a table of data and put all data, that meets a criteria, onto their own Worksheets. For the exercise I will use data as shown below.
Download Working Example We will write some VBA code that will place all data that fits under a criteria of "Administration" onto a Worksheet named "Administration", all data that fits under a criteria of "Marketing" onto a Worksheet named "Marketing" etc. To do this we will use the AdvancedFilter Method.First we need to name the table, including row 4 headings, MyData. Then we need to run the code as shown below on the data.
Dim rData As Range
Dim strName As String
Dim lLoop As Long, lCount As Long
Set rData = Range("MyData")
'Ensure column "G" is clear
'Filter out a unique list of Departments to range G1
rData.Columns(5).AdvancedFilter xlFilterCopy, , Sheet1.Range("G1"), True
'Count how many unique departments there are. This number is used _
to determine how many loops are needed.
lLoop = Sheet1.Range("G2", Sheet1.Range("G65536").End(xlUp)).Rows.Count
For lCount = 1 To lLoop
'Get the name of top department from cell G2 (G1 is a heading)
strName = Sheet1.Range("G2")
'Create a sheet named the same as the department. _
If sheet already exists halt macro
On Error Resume Next
Sheets.Add().Name = strName
If ActiveSheet.Name <> strName Then 'Sheet already exists
Application.DisplayAlerts = False
Application.DisplayAlerts = True
MsgBox "Sheet already exists. Macro stopped", vbCritical, "OzGrid.com"
On Error GoTo 0
'Use the unique list of Departments as the _
criteria for Advanced Filter
rData.AdvancedFilter xlFilterCopy, Sheet1.Range("G1:G2"), _
'Delete cell G2 so that next Department moves up
'Clear criteria heading
If you are not familiar with the AdvancedFilter method in Excel VBA read the help file. It is a very good method to familiarize yourself with and can help in many situations. Alsogo here and read the Excel Tips and Tricks section on Advanced Filter.You can download a working example of the above codehere
Are You Into Excel and Excel VBA?Our 4 Most Popular Bundled Savings
Until next month, keep Excelling!
Office Ready Professional 3.0 |High Impact e-Mail 2.0 |Office-Ready Business Plans |Office-Ready Marketing Plans |e-Marketing Suite |Office Policy Manual |Ultimate Everyday Calculator |Ultimate Financial Calculator |Ultimate Marketing Calculator |Template Zone Home Page |Office Ready Stuff It |Ultimate Loan Calculator
You are more than welcome to pass on this newsletter to as many people as you wish. Or even upload it (as is) to your Web site!
To no longer receive our newsletter, send a NEW email with the exact words "action: Unsubscribe" in the body of the email, or click here .
Microsoft and Microsoft Excel are registered trademarks of Microsoft Corporation
Read this issue and past issues online here :