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".

 

 

 

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.

 

 

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.

 

 

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).

 

 

 

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.

 

 

 

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.

 

 

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:

 

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.

 

 

 

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:

http://www.microsoftexceltraining.com/FreeNewsLetterArc.html   

 

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:

 

 

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:

 

 

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:

 

 

 

 

 

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:

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!