Saturday, 23 January 2010
OzGrid's Free Excel Newsletter
Getthe OzGrid Excel Plus Add-In! 8add-ins in one for less than you think. If you buy the add-in and are subscribedto our free newsletter, you will get the "File Size Reducer" for FREE!ManyOther Excel Add-ins HereOver 40 of them!
Allpast issues online here: http://www.ozgrid.com/News/Archive.htm
The newsletter isdivided into four sections:
1. What's new at OzGrid.com
2. Microsoft Excel tips
3. Microsoft Excel VBA tips
4. Helpful information
It ispublished on about the 10th of each month and always written bymyself Dave Hawley.
You are more thanwelcome to pass on this newsletter to as many people as you wish, all I ask isyou pass it on in it's entirety.
Should you wish to no longer receive ournewsletter, send an email with the words "action: Unsubscribe"in the body of the email, or clickhere.
Contained at the bottom of eachnewsletter is Helpful information on how to get the most from ournewsletters. If you think there is something missing, please let meknow.
Microsoft and Microsoft Excel are registered trademarks of MicrosoftCorporation
What's new at OzGrid.com [Top]
Herewe are in July already. Well, it has been the end of the financial yearhere in Australia (30 June) so as you can imagine, we have been extremely busyfulfilling client obligations. Hopefully this hectic period is coming toan end and we can now concentrate on getting our books in order to please thetax man, before returning to our normal "really busy" state, asopposed to our "flat out like a lizard drinking" state as they say inOz.
Ahuge congratulations to our Brazilian subscribers for winning the World Cup(again). While the sport is not that big in Australia, I think it gets biggereach time the World Cup comes around. I guess we Australians are a bit like theAmericans in that once (we will one day) we finally make it to the Cup the sporthere in Australia will get the recognition it deserves.
Online Lessons [Top]
Hopefullyour on-line lessons will be up and ready to go at the end of this month. We have spent lots of time over the last few weeks checking and updating things,and working with our web developer to get the lessons into an easy-to-use,user-friendly format. The first package we will be putting on line is ourMicrosoft Excel - Level 2 course as this is one of our most popularcourses. If all goes how we hope it will, we will follow this with all ourother training courses. People wishing to access on-line lessons will besupplied with a Username and password, so they can access lessons at theirconvenience. We are hoping that many people will take advantage of this,as although we try to send lessons within one working day, many clients haveasked if the on-line feature would be available, so they can be totallyindependent of Ozgrid, and only contact us if they have anyquestions.
Inlight of the on-line lessons becoming available, we will be reviewing our pricesover the next few months. We will be offering a price to purchase trainingcourses with all questions answered, and a price for just supplying the lessonsonly, with no ability to ask questions. We hope to publish these newprices in the September newsletter.
FiscalYear Templates [Top]
As itis the end of the financial year here, we thought that the following templatessupplied by Microsoft may aid some of our readers. Please be aware though,we have not checked them out personally at this stage. If you decided tocheck them out, we would be interested to know what you think of them.
MicrosoftExcel Add-ins and Free Stuff [Top]
Those of you that were subscribed lastmonth will recall the launch of our Excel add-ins Excel Plus Add-In!and File Size Reducer (http://www.ozgrid.com/Services/AddinExamples.htm ). Wehave some fantastic feedback on both these add-ins, not only are you getting 9add-ins for only $20.00 but the "File Size Reducer" comeswith it Free.
We have also created a page on oursite dedicated to other Excel Add-ins from all over the world. At present thereare some 40 add-ins here that you can purchase them on-line immediately.You will find this page here: ManyOther Excel Add-ins Here (http://www.ozgrid.com/Services/ExternalAddIns.htm). This page is very LARGE so it can take a while to download.
There is no denyingthat we all love something for nothing! So I have decided to create apage on our site dedicated to nothing but FREEstuff for Excel (http://www.ozgrid.com/Services/ExternalFree.htm)I will be adding to this site as I come across and develop free Excel stuff.Please take note of the "Credit To" links as most of these are fromother sites and not just mine.
MicrosoftWord Expert Newsletter [Top]
The Word Expert: http://www.thewordexpert.com/Anne, or Dreamboat as she is known on the WWW
Has recently started a FREEmonthly newsletter that gives subscribers lots of info on Microsoft Word as wellas other Office Applications. If you want to check out this months issue Click here for the July Newsletter! The author, Dreamboat is genuine, and the newsletter worthwhile. Personally Ifeel this newsletter is the perfect compliment to our Excel newsletter.
CreditCard Facility [Top]
Thought I would just let all youfledgling businesses know that after the hassle of searching for and signing upwith a suitable company that supplies secure on-line Credit Card facilities, ithas proved to be extremely worthwhile. We are using WorldPay for ourCredit Card facilities, and this facility has already paid for itself in theshort time that we have had it. WorldPay are prompt with payment, alltheir statements and correspondence are well laid out and easy to understand,and most of all, their charges are reasonable. This was a big thing for us,because even though we are based in Australia, our costs and charges are in USdollars, so because of exchange rates etc., we wanted to keep the charges to aminimum. I would estimate that already 70% of our customers are using ourcredit card facility, with more using it every day. This saves us lots oftime in itself, because the bank charges for overseas cheques in a foreigncurrency, or telegraphic transfers are horrendous here in Oz. Not tomention the fact that I no longer have to get in my car and drive to the bank todeposit cheques all the time, and I no longer have to que at the "foreigncurrency" teller at the bank, getting frustrated at their inadequacy, manytimes a week. This has been cut right down to an acceptable level andmakes me a happy woman.
NewAdditions to our Web Site [Top]
PagesWith New Content Added [Top]
Microsoft Excel tips[Top]
I thought for this months Excelsection we would look at looking up values from within a table or list. As thisis such a big topic, this will be part one of three. Beforewe jump into the functions we can use for this I would first like to point outhow a table or list should be set up in Excel. If we following these guidelineswe can easily extract data from any table or list.
Now of course these rules are nothard and fast, but more like guidelines. As I always say "good habits arejust are hard to break as bad ones".
If you would prefer all thebelow was done for you then Download the workbook here.
For all examples lets use a simple6 row by 5 Column table of data. In row B6 (yes leave rows 1 to 5 blank)put the first 5 months of the year. In cell B6 type: 1/1/2002 andcustom format as mmmm. Now right click on the bottom right corner of cell B6 (the fill handle), drag to cell F6, release and select "FillMonths". Now bold these headings. In cell A7, type: Monday nowuse the Fill Handle of A7 (left click this time) drag down to A13and release. Bold and Italic these. Now fill the table with the numbers, type 50 in cell B7, 75 in cell B8 and 225in cell C7 and 250 in cell C8. Select cells B7:C8and using the Fill Handle drag across to Column F, then down to Row 13.
Now select the entire table(A6:F13), go to Insert>Name>Create check both"Top row" and "Left column" and click OK. While thetable is still highlighted click in the Name box (left of Formula bar) and type: Data. Ideally we would use DynamicRange Names.
Ok, onto the extracting of datafrom the table. In most cases we can use the VLOOKUP function, which will return aresult by looking in the first Column of a table for a specified value andreturn the result from the corresponding row in a specified column to the right.Lets say we want to know the figure for Tuesday in March, we coulduse:
Very easy to use and very handy ina lot of cases. However the VLOOKUP relies very entirely on us knowing theposition of the column headed "March". With a table like thiswe could also simply use:
This holds a few advantages on theVLOOKUP with these being:
While the VLOOKUP is arguably the mostpopular Lookup function in Excel, it lacks the alibility to look to the left ofit's table_array thatit references. This is no problem for us to do in Excel though as we can use acombination of 2 other Excel Lookup functions, INDEX and MATCH
There are 2 INDEX functions in Excel and it's thefirst one we are going to use. It returns the value of a specified cell or array of cells within array.
The MATCH Function returns the relative positionof an item in an array that matches a specified value in a specified order. UseMATCH instead of one of the LOOKUP functions when you need the position of anitem in a range instead of the item itself.
So what we will do is use the MATCH function tosupply the argument of the row_num withinthe INDEX function. So if we use the same table again we can now look in ANYcolumn of our table and return the result from the corresponding row in theColumn to the left or right! So if we used:
We could look in the March column for the value 450and have our formula return the day of the week this value corresponds to. By changingthe very last argument column_num canmake it return the corresponding result from any column in the table. It isimportant to note the 0 usedas the match_type argument inthe MATCH function. This tells MATCH to find an exact match for 450 in the MarchColumn. We must use +1so we account for the headings. It can also take the value of 1 or -1. See text below from Excel help
Let's assume now that we do not know which columnwe want our corresponding result from. All we know is we want the correspondingresult for January, but we do not know which Column January is. The formulabelow will do this for us:
Beforethis will work however, we must name the range that holds are headings (B6:F6) FirstMonths.Note also that we have used VALUE("1/Feb/2002") as the argument for the second Match function. We must use this as ourheadings a true dates and not just text. If you are not sure what I mean by this thenI would urge you to read this page (and the links from it) http://www.ozgrid.com/Excel/ExcelDateandTimes.htmas Dates and Times are a very important part of Excel. Once you understand howexcel sees dates and times, you should have no more problems with them. We alsoneed to use +1 afterthe second MATCH because the named range "FirstMonths" has one lessColumn than the named range "Data".
Download the workbook here.
Microsoft Excel VBA tips[Top]
As we have just covered the use ofExcel's Lookup functions in our Excel section, it would be a good time to pointout a common mistake made by some programmers. If you wish to perform a Lookupin VBA it seems that many opt to use the Vlookup WorksheetFunction in theircode. While this can be done, it is a slow means of looking up data. This isbecause when we are in VBA we can use the extremely fast Find Method. However,before I show an example I fell it is extremely important to point out a coupleof very often overlooked parts of this Method. See below from the Excel Help:
The settings for LookIn, LookAt, SearchOrder,and MatchByte are saved each time you use this method. If youdon’t specify values for these arguments the next time you call the method,the saved values are used. Setting these arguments changes the settings in the Finddialog box, and changing the settings in the Find dialog box changes thesaved values that are used if you omit the arguments. To avoid problems, setthese arguments explicitly each time you use this method.
If we do not remember this we are heading into possible problems, bigtime! I cannot tell you how many times I see the Find Method usedincorrectly due to this. By far the best way to get the code needed for the FindMethod is to use the Macro Recorder then modify the code.
Let's assume we have a table of data on Sheet1 within a range named Data and We wish to find the name Billy Brown obtain the valuefrom 3 columns to the right on the correspoding row.
' FindBillyBrow Macro
' Using the Find Method over Vlookup
If WorksheetFunction.CountIf(Sheet1.Range("Data"), "Billy Brown") > 0 Then
vOurResult = .Find(What:="Billy Brown", After:=.Cells(1, 1), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
Note that we have used the CountIffunction to first find out whether the name even exits. The code does assume thenamed range Data exists on Sheet1 (CodeName). You will find this method, whileit looks slower, is many times faster than VlookUp. It is also very flexible aswe can offset to the right or left, depending on where we find the name. On thepoint of 'shorter code', it is a common misconception that the shorter the codethe more efficient it must be. Nothing could be further from the truth! Oftenshort code accounts for no errors and doesn't make use of Excel's built infeatures.
Let's now make this a bit more complicated bysaying we might well have more than one occurrence of the name Billy Brown.The code as it is will only find the very first occurrence, which may not be whatwe want. Let's assume we want the name Billy Brown and it's correspondingresult on the same row, 3 columns to the right BUT only if the value in the cellimmediately to its left is 35 (the persons age).
Dim rFoundIt As Range
Dim iLoop As Integer
' FindBillyBrow Macro
' Using the Find Method over Vlookup
'Set variable to start search from
Set rFoundIt = .Cells(1, 1)
'Loop no more times that the name occurs
For iLoop = 1 To WorksheetFunction.CountIf _
(Sheet1.Range("Data"), "Billy Brown")
'ReSet variable to found occurence of name. Next loop search _
will start AFTER this Set cell.
Set rFoundIt = .Find(What:="Billy Brown", After:=rFoundIt, _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
'Check if it's the right one.
If rFoundIt.Offset(0, -1).Value = 35 Then
vOurResult = rFoundIt.Offset(0, 3)
Exit For 'Leave loop
End With 'Sheet1.Range("Data")
If Not IsEmpty(vOurResult) Then 'Variable holds a value
Again while this code is quite lengthyfor what it does I feel confident in saying there is no faster way. (now I'll beflooded with emails!!)
The important parts of this code arethe use of CountIf to limitour loop and the use of Exit For Notealso the use of the IsEmptyFunction which returns a Boolean value indicating whether a variable has beeninitialized.
SomeShort and Sweet VBA Tips
OK, that's it for this month - until next month - keep Excelling!
Getthe OzGrid Excel Plus Add-In! 8add-ins in one for less than you think. If you buy the add-in and are subscribeto our free newsletter, you will get the "File Size Reducer" for FREE!ManyOther Excel Add-ins HereOver 40 of them!
HireOzGrid For Excel/VBA