OzGrid Excel Add-in Collection
OzGrid Excel Newsletter
It ispublished on about the 10th of each month and always written bymyself Dave Hawley.
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.
Youare more than welcome to pass this on to as many people as you wish.
What's New at OzGrid.com| New/Updated Pages| Training Special| Microsoft Excel Tips
Excel Quick Tips | Microsoft Excel VBA Tips| VBA Quick Tips | Helpful Information
OzGrid Excel Add-in Collection
What's new at OzGrid.com [Top]
Hi All,
Another month has just flown by - and as usual, our feet haven't spent much time on the ground. This month we have been busy trying to get more lessons ready to place on-line. We are certainly keeping our web developer in the life of luxury that he thinks he deserves!!!
You will also have noticed that Dave has been spending lots of time updating the web-site and re-organising things. we feel that in order to keep our clients enthused and to encourage new traffic to our site, we need to spend lots of time keeping the content on our site "fresh", which is why things change frequently. Any feedback would be welcomed, we are interested to know what other people think.
Check out these new/updated pages: [Top]
If your workbooks are slow to open, slow to close or slow to recalculate, then on this page you will find lots of tips and tricks to make life much easier for you. There are lots of reasons as to why workbooks slow down, check this page out and find out how you should set up a spreadsheet efficiently in the first place, then you will no longer have to cope with painfully slow workbooks.
We have bundled together all of our OzGrid Add-Ins here as one entire collection, and located in one spot. Buying the collection means you save USD20.00
This page is mainly non-OzGrid Add-ins. The idea is to get all the Add-Ins available for Excel in one spot for easy access by other users/developers. At present there over 60 Excel Add-ins here making it one of the largest Excel Add-in pages on any Excel site in the world!
Excel Smart Tools and Forecasting
Here you will find two Excel Add-ins ExcelSmartTools Premium Edition and ForecastingTools Graph, both by M.G.Moreira. If you need to use add more functionality and formulae to Excel, or you do forecasting, check out this page.
As most of you are probably aware, at OzGrid.com we are very sure that our products, information and service is of a high quality, unique and totally focusedon Microsoft Excel. This is why we offer links to affiliated sites.
Let OzGrid suggest a book for you based on you current skill level. As Excel spreadsheet developers and trainers we know which one will suit you. Email[email protected] and tell us your current skill level.
If you want free stuff for Excel, whether it be Workbooks, tutorials or Add-ins etc head straight here. The ultimate aim for us, with this page, is to have all free Excel stuff together in the one spot. If you know of any free Excel stuff, or have some yourself, pleaseemail me so I can consider it for inclusion.
Our training special that has been running for about a month now will be finishing on 30 September. We had anticipated ending it by the time this newsletter was sent, but there have been so many clients that cannot pay until the end of this month, that we have extended it.
Course prices are shown below:
*$55.00 for each additional participant
OrderOnline - http://www.ozgrid.geo.net.au/payment.php4
All Online Courses can then be accessed by going to ExcelTraining - http://www.ozgrid.com/Training/default.htmand clicking the Log In button
Last month and the Month before we looked at Excels Lookup and Reference Functions. We covered such Functions as VLOOKUP, MATCH and INDEX. We saw how we could nest (remember nesting means using the result of one function to supply the argument of another) these functions together to obtain a result from a table of data that met almost any given criteria. We also looked at how we could look left, look right and even look left or right, depending on where are needed Column was. There was also the (often forgotten about) Intersect method where we used: =Tuesday March with both Tuesday and March being named ranges.
We also made good use of some dynamic named ranges and even had one that would always expand down as far as the longest column of data in a Table. We combined this with the List option in Data Validation to make a very user friendly lookup table, sort of like a mini Pivot Table.
If you missed these two issues, or were not subscribed, they (and all past issues) can be read online here: Newsletter - Archives -http://www.ozgrid.com/News/Archive.htm Issues 15 and 16.
Ok, in this, part 3 of 3 for Lookup and Reference Functions, we will look how we can use Excel's Lookup functions to retrieve information when we have more than one occurrence of specific data and we want to nominate which occurrence we want. We will also look at how to use Lookups to match 2 criteria.
As with part 1 and 2, there is a Workbook you can download here:
Download the workbook here - http://www.ozgrid.com/download/SepNewsletter.zip
You will see that the Worksheet "September Examples" houses a very simple 2 column Table. Column 1 (Color) has different color names, with some repeated up to 3 times. The second Column (Occurrence) contains a number that represents the occurrence of the color name. The Table (including headings) occupies the range B6:C17
Let's assume we want to find the second occurrence of the color Blue and return it's corresponding occurrence number, which will be 2.
Here is the Formula we could use:
=VLOOKUP("Blue",INDIRECT(ADDRESS(MATCH("Blue",$B$6:$B$17,0)+6,2) & ":$C$17"),2,FALSE)
If we wrote the VLOOKUP Function without nesting the other Functions it would look like:
=VLOOKUP("Blue",$B$8:$C$17,2,FALSE)With $B$8:$C$17 being the important part
Let's break this down so we can understand it. We MUST start to look from B8 and NOT B6 as we have the very first occurrence of Blue in B7 so our lookup MUST start after this cell. This is where the ADDRESS and MATCH function come into play. Remembering the syntax for ADDRESS is:
=address(row_num,column_num,abs_num,a1,sheet_text)
Non bolded arguments are optional and we have omitted them. The row_num argument is being supplied by the MATCH Function, ie MATCH("Blue",$B$6:$B$17,0)We have used it to tell ADDRESS which row number we want. Note that we have added6 to the MATCH result, this is because the row number returned from MATCH is relative to its Lookup_array ($B$6:$B$17) and MATCH will find Blue in cell B7 and result in 2, we actually NEED 8because VLOOKUP MUST start it's search AFTER the first Occurrence of "Blue". For thecolumn_num argument we have simply used 2 because our left most column of our Table is Column B. So ADDRESS results in $B$8, we then Concatenate this with ":$C$17"resulting in $B$8:$C$17
Next we nest the nested ADDRESS and MATCH function into the INDIRECT function so that Excels sees $B$8:$C$16 as an range address rather than a text string. Now this is used to supply the Table_array argument of VLOOKUP.
The trouble with this approach is that we can only use it to find the second occurrence efficiently. To find the third occurrence, or greater, our formula would become far too large to be practical. Remember how we looked at correct ways to set up Tables in Excel, in particular the practice of always leaving at least 4 blank rows at the top of our table. Well, I have also left one blank Column (A) to the left of our Table so we can make use of it, here is how. In cell A7 enter the formula: =B7&COUNTIF($B$7:B7,B7) now copy this down to row 17. Very importantly note the Absolution of $B$7
If you haven't guessed already, we now use: =VLOOKUP("Blue3",A6:C17,3,FALSE)to find the third occurrence of Blue. Column A can be hidden if we wish and we could also convert these formulae in Column A to values only if we wish. Just realise the this would mean it is no longer dynamic.
The use of the Concatenate in a spare Column can also be used to allow us to find the occurrence of two or more criteria. This would simply be done by using:=B7&C7 and copied down then we simply use a Lookup function to locate the needed joined text. This is also shown in the example Workbook.
Quick Excel Tips [Top]
Needto retain a Table with filtered rows, that are hiding data you do not want printed, or simply to look at? Set up the Table with the rows not wanted hidden by Excels Auto Filters (Data>Filter>AutoFilter), or hide them manually. Now go to View>Custom Views Click Add, give the view a name and click OK. Next time you need this view go to View>Custom Views select the View name and click Show. The great part about Custom Views is, you do not even need the Auto Filters on next time you show your view.
Need a special Character in a cell and don't want or have the Windows Character Map? Push Alt+F11, then F1, then type ascii then select Character Set (0 – 127) and/or Character Set (128 – 255) To get the character you need in a cell, hold down Alt+0 then push the Character number. Eg to get the degree symbol, hold down Alt and push 0176
Imported some data into Excel that has some strange characters (eg ☺) and you want them removed, but you don't even know what they are or how to get rid of them? Select the cell, then copy the character from the Formula bar and paste it to a blank cell, say A1. Now in any cell put =CODE(A1) this will return the Character number! Now select your imported data, go to Edit>Replace, in the Find what: hold down Alt+0 <type the Code number returned by the CODE Function>. Leave the Replace with: box blank and click Replace All.
Are You an Excel Whiz? Find out by taking the quiz!
http://office.microsoft.com/assistance/2002/quiz/qxlgeneral.aspx
Microsoft Excel VBA tips [Top]
I thought for this month we would look at how we make our VBA projects have that 'polished finish'. We can do this by creating a Splash Screen that shows upon the Workbook opening for about 5 seconds, then closes itself automatically. This is amazingly simple to do, even if you have have no VBA knowledge at all!
Sub KillForm()
Unload UserForm1
End Sub
Your code in the UserForm (Step 10) should look like:
Private Sub UserForm_Initialize()
Application.OnTime Now + TimeValue("00:00:05"), "KillForm"
End Sub
Now all we need is some code in the Private Module of the Workbook Object (ThisWorkbook). In the Project Explorer (left of screen) you should see the name of your Workbook. Expand out the folders branching off the bottom of it, until you see ThisWorkbook under Microsoft Excel Objects, double click ThisWorkbook. Now in this Private Module place:
Private Sub Workbook_Open()
UserForm1.Show
End Sub
Now push Alt+Q to get back to Excel, Save and close the Workbook. Now re-open it and see your splash screen in action!
For the more advanced VBA users out there, let's look at one very common problem with a ListBox Control, that is how to code it if the MultiSelect Property is set to fmMultiSelectMultiand/or we have more than one column of data. I have created a Workbook example of this which demonstrates just how we can make use of a ListBox that has these attributes.
You will see from the Workbook that I have coded it so a user can easily select which item(s) to delete from a Table. They are also given the option of deleting: 3 columns*1row or 2 columns*1 row or 1 column*1 row. There is also a Checkbox to toggle selecting all/none of the items. Before you look at the code behind the ListBox, please read the text below from the Excel VBA help.
The ListCount property is read-only. ListCount is the number of rows over which you can scroll. ListRows is the maximum to display at once. ListCount is always one greater than the largest value for the ListIndex property, because index numbers begin with 0 and the count of items begins with 1. If no item is selected, ListCount is 0 and ListIndex is –1.
The Selected property is useful when users can make multiple selections. You can use this property to determine the selected rows in a multi-select list box. You can also use this property to select or deselect rows in a list from code.
The default value of this property is based on the current selection state of the ListBox. For single-selection list boxes, the Value or ListIndex properties are recommended for getting and setting the selection. In this case, ListIndex returns the index of the selected item. However, in a multiple selection, ListIndex returns the index of the row contained within the focus rectangle, regardless of whether the row is actually selected.
When a list box control's MultiSelect property is set to None, only one row can have its Selected property set to True. Entering a value that is out of range for the index does not generate an error message, but does not set a property for any item in the list.
END OF EXCEL HELP
Now that is a clear as mud :o) you can download the example Workbook.
Download -http://www.ozgrid.com/FreeDownloads/MultiListBox.zip
VBAQuick Tips [Top]
Stop users from scrolling around a Worksheet?
Private Sub Worksheet_Activate()
Me.ScrollArea = "A1:K25"
''Uncomment line below to get back to normal
'Me.ScrollArea=""
End Sub
Generate 6 random non-repeating dice throws. Only good for 6 numbers, if you add many more you will be waiting a while :o) If you need more, see:http://www.ozgrid.com/VBA/RandomNumbers.htm
Sub ThrowDice()
ActiveSheet.Calculate
Do Until WorksheetFunction.CountIf(Range("A1:A60"), "TRUE") = 6
ActiveSheet.Calculate
Loop
End Sub
OK, that's it for this month - until next month - keep Excelling!
Kind regards
Dave Hawley
Youare more than welcome to pass this on to as many people as you wish.
Should you wish to no longer receive ournewsletter, send an email with the words "action: Unsubscribe"in the body of the email, or clickhere.
Readthis issue and past issues online here: http://www.ozgrid.com/News/Archive.htm
Microsoft and Microsoft Excel are registered trademarks of MicrosoftCorporation
Excel Book Search