OzGrid.com Excel Newsletter
To unsubscribe from this monthly newsletter, please send a blank email with the exact word: unsubscribe in the Subject field. Please ensure you use the email address you subscribed with. mailto:[email protected]?subject=unsubscribe >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Your are more than welcome to distribute this newsletter to as many people as you wish, all I ask is you send it in it's entirety.
Hi all
This month has come around all to quick, I'm glad I managed to sent last months newsletter early! Before we go into any detail on this months newsletter I would again like to extend our sincere condolences to any person effected by the terrible events of September 11 2001. As I write this newsletter I have my television tuned into the events that are now unfolding as a US lead coalition launches attacks on  Taliban military installations near several key Afghan cities, including Kabul and Kandahar. I know I speak for the vast majority of Australians when I say "We as Australians are proud to be able to assist in anyway possible in ridding the world of all terrorist activity".
Latest New for OzGrid
www.ozgrid.com coming soon!
On a much lighter note, the big news for OzGrid is the nearing of the launch of our new Web site. I have been working very closely over the last month with a local Web designer, Hans Stammel of  AVIP  (http://www.avip.com.au/). Hans has reinforced my believe in the quality of service you are able to get from a small business as apposed to a large one! AVIP will also be hosting our new site. The main focus of our new Web site will be a clear concise Web site that enables people from any part of the globe to find any needs or wants they have specific to Microsoft Excel. We made a decision some time ago that we would not expand into other areas of computing, be it Word, Access or Powerpoint etc. Why? Simply because we want to become number one in a niche market and be the best at what we do and I don't believe this happens when you spread yourself too thin. This again goes back to the point I made previously about "quality of service you are able to get from a small business as apposed to a large one". If all goes well our new Website should be fully operational by the beginning of November 2001, isn't that right Hans? :o) Our Web address will still be http://www.microsoftexceltraining.com but as we also own the domain ozgrid.com http://www.ozgrid.com will also get you to our new Web site. If you go to www.ozgrid.com now, the pages you see are only a front and do not fully reflect the current status of our new site.
A Very Bad Experience!
At present our Web host is homesteadprofessional.com and without going into any detail, the reason I am leaving them is because I have been (and still am) totally dissatisfied with their service. If any of you had tried to access our Web site via our ozgrid.com domain FOUR MONTHS prior to last week, you would have ended up at Homesteads Web site, this is because they hijacked our domain name and pointed it to their site! This is just one of the many reasons I am leaving them and warning others of potential problems of using them. I better stop myself there, in case I say something I regret :o) I have Hans of AVIP  to thank for wrestling our domain name back for us.
The Aussie Dollar $$$$$ is Down!
Being an Australian based business all our costs are in Australian dollars (AUD). What this means for our non Australian clients is that our prices at present are very competitive, especially for our US clients! At the time of writing 1 USD is equal to 1.97568 AUD, so any prices we have can be basically be halved to get the USD equivalent. To see what it works out to in your currency, go here: http://www.ozgrid.com/Services/CostandService.htm. I apologise to Netscape users as I'm not too sure it will work for them. On the subject of costs, we will be offering a credit card facility on our new Web site early in the new year. This has been a long time coming, but has not been in our full control due to our current Web host (homestead).
15-Oct-2001 to 27-Oct-2001
What's the deal with these dates? Unfortunately OzGrid will not be open for business between these dates as myself and my business partner will be out of the country. Should anybody be wishing to contact us between these dates, please do so and we will reply as soon as possible after returning.
New Associate
To enable us to provide a full service for Microsoft Excel we are proud to announce that we have associated ourselves with a password recovery company password-find.com (http://www.password-find.com/). They are also a small business that has a very similar focus as OzGrid.com. After corresponding with Graeme Woods of password-find.com several times I was very impressed with what he had to say about his business and his outlook. It is for these reasons that I am proud to be associated with them. They offer a service that will be invaluable for those of us that have lost our password(s) for Excel Workbooks, Word Documents and WinZip files. As a bonus you will find their prices extremely competitive! Please note this is not an affiliate program and I receive not money at all for any business that may result from our association with them.
Microsoft Excel
Let's get down to this months Excel tips! I thought that this month I would look at a Function that is the perfect compliment for Excels Auto Filters. I believe I would be safe in saying that Auto Filters are arguably one of Excels most useful features. I know I make heavy use of them, not only within the Excel interface, but also in VBA for Excel. The Function I am referring to is Excels SUBTOTAL Function! What this Function enables us to do is, perform any one of 11 different functions on a list or database. Before we go into detail on this Function it is important to note that I am NOT referring to the Subtotal feature from the Data menu. The Subtotal feature from the Data menu will insert the SUBTOTAL Function for you, but I believe that we should first understand how it works, hence my explanation of the SUBTOTAL function from the Paste Function dialog (Shift+F3). Once you understand this, you will automatically understand the Subtotal feature from the Data menu.
The SUBTOTAL function can take up to 30 arguments, but most often you will only supply the minimum, which is two. The syntax for the SUBTOTAL is: SUBTOTAL(function_num,ref1,ref2,...). The function_num can be any one of 11 different calculations that can be used in calculating subtotals. The ref1,ref2,.. etc are references to up 30 ranges you wish to subtotal. the 11 different calculations that can be used in calculating subtotals and their function_num are as below:
Function_Num          Function
1                              AVERAGE
2                              COUNT
3                              COUNTA  
4                              MAX
5                              MIN
6                              PRODUCT
7                              STDEV
8                              STDEVP
9                              SUM
10                            VAR
11                            VARP 
All of these functions are Functions in their own right, so if you are unsure of their use, simply push F1 and type their name into the Excel help.
I mentioned that the SUBTOTAL function is the perfect compliment for Auto Filters and the reason is because the SUBTOTAL function will only include non-filtered (or non-hidden rows) in the range it refers to. This is very easy to see by typing the numbers 1 to 10 in A2:A11, type any text heading in cell A1 and then place:
=SUBTOTAL(9,A1:A11) in cell A12. The "9" is telling SUBTOTAL to use the SUM function. We can include the heading in the reference as text will be ignored in the SUM. Now, while you have any cell within the range A1:A12 selected, go to Data>AutoFilter and Excel will place a single Auto Filter arrow in cell A1. At present our SUBTOTAL should be returning a result of 55 (the SUM of 1 to 10). Now select the Auto Filter arrow and choose "Custom", select "is greater than" from the top left box (filter operation box). From the top right box select "3" and then select  "is less than" from the bottom left box. Select "8" from the bottom right box and ensure that the "And" option button is checked. Now click "OK". The visible cells should now contain; 4, 5, 6 and 7 and our SUBTOTAL will be returning 22 i.e.. the sum total of 4, 5, 6 and 7. So as you can see, while our reference for the SUBTOTAL is still A1:A11 it is not including any filtered rows.
There are three very important points to realise with the SUBTOTAL function and they are:
1.    SUBTOTAL will only ignore rows that are hidden as the result of the Auto Filter. However, having said this, if you hide a row that is part of a filtered range it will ignore it, but only if the Auto Filter is already in use.
2.    If there are other SUBTOTAL functions within you reference range(s), their results will be ignored. This prevents any double counting.
3.    You cannot use SUBTOTAL on a 3-D reference. A 3-D reference is a range of cells that spans more than one Worksheet.
Once you have grasped the concept of the SUBTOTAL function, have a go at using the Subtotal function under Data on the Worksheet Menu Bar.
Excel VBA part 2 of Select Case
In last months newsletter I demonstrated the benefits of using a Select Case statement in VBA. For those of you that were not part of the newsletter list last month, you can see part one here:
Let's pick up where we left off and get straight back to into it. Let's assume that you only want to perform some action if the range(s) you are checking are between 2 numbers. If this is the case (excuse the pun) then you could use:
Sub TheSelectCase5()
  Select Case Range("A1").Value
    Case 100 To 500
         Range("B1").Value = Range("A1").Value
    Case Else
         Range("B1").Value = 0
  End Select
End Sub
In this instances if the range A1 contains a number => 100 and =< 500 then cell A1 value will be placed into cell B1. For those of you that are not familiar with my methods, I avoid copying whenever possible as it's too messy and can slow down code.
Now, what about if we needed to check if cell A1 was not only between 100 and 500 but also between 700 and 1000, 1500 and 2000? No problem, with the Select Case you would simply use:
Sub TheSelectCase6()
 Select Case Range("A1").Value
   Case 100 To 500, 700 To 1000, 1500 To 2000
     Range("B1").Value = Range("A1").Value
   Case Else
     Range("B1").Value = 0
 End Select
End Sub
In other words if cell A1 contains a number (eg 600) that does not meet the Case criteria, B1 will equal 0.
By- the-way, the limit using a Select Case like this is far from 3 criteria! You can also mix and match the criteria you use in a Select Case Statement, so if you wanted to include some text and/or specific numbers you could use:
Sub TheSelectCase()
 Select Case Range("A1").Value
   Case 100 To 500, 652, 700 To 1000, 1233, 1500 To 2000, "dog", "cat"
     Range("B1").Value = Range("A1").Value
   Case Else
     Range("B1").Value = 0
 End Select
End Sub
The above example would also check cell A1 to see if it contained 652,1233 or the text "cat" or "dog". Do the same using If, And, Or and you will end up with a very long hard to decipher Procedure.
The last benefit we will look at with the Select Case Statement is how we can use it on text and determine if it lies between other text in an alphabetical sense.
Sub TheSelectCase7()
Select Case Range("A1").Text
  Case "aardvark" To "elephant"
     Range("B1").Value = "it's between"
  Case Else
     Range("B1").Value = "it's not between"
 End Select
End Sub
What this example will do is check whether the Text in cell A1 would fall between "aardvark" and "elephant" in an alphabetic list. So if A1 had the text "zebra" B1 would equal "it's not between", however if it contained "cat" B1 would equal "it's between". One  point that should be made here is that, by default, VBA code like this, is case sensitive (Binary). So if cell A1 had the text "Cat" as apposed to "cat" B1 would equal "it's not between". This because the internal binary representations of the characters in Windows are seen in a table something like:
A < B < E < Z < a < b < e < z < < < < < <
This can at times cause unexpected results, but like most parts of Excel we can change this. I will show you how this can be done next month when we look comparing values using wildcard characters. So, until next month keep Excelling!
Dave Hawley
OzGrid Business Applications
http://www.ozgrid.com Coming Soon!
Remote Excel and VBA training
Task Automations
Project Enhancements
Free Excel Newsletter
Help Desk
Spreadsheet Designs
If it's Excel, then it's us!