All Excel Templates Super Special $189.00! New Templates Added!
We Recommend SmartDraw for Flowcharts NewsletterSubscriber Specials
 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 |
What's New at OzGrid | Excel Tips and Tricks | Excel VBA Tips and Tricks What's New at OzGrid
This last month we are still continuing with a major change to our website with the upgrade of ourExcel Forum . The Forum has grown so much since its inception that the upgrade is essential to the continuing growth of the Forum and OzGrid. Much of this can be attributed to our forum moderators and our regular Excel gurus. To them a HUGE thank you from all of use here at ozgrid.
The Forum upgrade was necessary in order for us to be fully compatible with our web-server and will allow greater flexibility for both users and administrators alike. The only drawback is that it will take Search Engines a while to spider and reindex the new URLs for each past posting, so hits on the website are down at the moment. However, we feel that it was a necessary upgrade and it should only take a few months for us to be back on track and ranking highly.
We should receive some statistics on the sale of our book (Excel Hacks )at the end of June. So far, all our feedback has been fantastic, so hopefully the statistics will be favourable. We also have an association with Amazon and have set-up astore here All proceeds fro this store will be used to enhance the question forum and keep it totally free.
We are currently updating all our Training Lessons (for Excel 2003) and the new improved Level 1 course will be available within the next month. If you are a forum member you will soon be able to take the training in a forum category at a discount. You can take training now at a discount viathis page
There has been a great new site recently launched here: Better Excel and Word Solutions for all Microsoft Office users
That's all for this month, we hope you enjoy the newsletter
**IF YOU ARE INTO MICROSOFT ACCESS***
TheEasy Access Database Directory is a non commercial enterprise, which offers a free directory service (including a free listing for anyone, whether they are a commercial business or just an enthusiast) in relation to Microsoft Access resources, which can be easily browsed by category or via key word searches.
In keeping with lastmonths newsletter on Excel (Fill Handle)Tips andTricks, this month I thought I would list some Excel formula/function tips and tricks.
**AUTO SUM**
Perhaps one of the handiest Excel Functions is the Sum Function. As you are now doubt aware, it can be used to sum a range of cells.
Excel makes this function (and now also the COUNT, AVERAGE, MAX and MIN) very easy to use by the use of the AutoSum feature.
Let us say we have a list of numbers in the range A1:A10, with A1 being a Text heading. Select cell A11 and click the AutoSum icon (Sigma symbol) on the Standard toolbar. You will end up with =SUM(A2:A10). The reason it Start s at A2 is because Excel sees that cell A1 is text.
The AutoSum feature will always first look in the cells above and always Start from the next cell immediately below either the first blank cell, or text cell. If there are no numbers is the cell(s) directly above it will look to the left of the selected cell.
This is the AutoSum feature in it's simplest form. If we had some numbers in the range A2:D10 (row 1 are headings) we could select the range A1:E11 and click the AutoSum icon and Excel will place the function in the range A11:D11 and also in E2:E5. This can save lot's of copying and pasting of formulas.
**AUTO SUM ON FILTERED RANGES**
If we have a list of numbers that has had the Auto Filter applied Excel will not simply use SUM but the SUBTOTAL Function. For example; =SUBTOTAL(9,A2:A10) which will sum ONLY the non-filtered cells (visible cells) in A1:A10. Unfortunately, it will only do this if using SUM, even though there are SUBTOTAL equivalents for the others. See the links below for details on the SUBTOTAL function and some advanced uses;
**FUNCTION MEMORY JOG**
If you are like me (bad memory) there will be times when you wish to use an Excel Function but cannot remember the correct Syntax. For example, you know how to use the VLOOKUP function but cannot recall the Syntax. In the cell that is going to house the function, type =vlookup( now push Ctrl+Shift+A and you will see: =vlookup(lookup_value,table_array,col_index_num,range_lookup)
You can also click the Function icon, left of the Formula bar and Excel will display the Function argument dialog for VLOOKUP. It's important to note that the argument names which are bolded are mandatory, while non-bolded ones are optional. To get more detailed help, click the Function icon, left of the Formula bar, or the "Help on this function" link.
**UNDERSTANDING FORMULAS & DEBUGGING THEM**
Sooner or later you will need to debug or understand a formula that has *nested functions. For example, you may have a *nested function as shown:=VLOOKUP('Animal Names'!C16,MyData,MATCH("Animals",MyHeadings,0),FALSE)
This Formula has the MATCH Function nested into the col_index argument of the VLOOKUP function. It also uses two named range (MyData and MyHeadings).
*A nested function is where the result of one formula is used to supply the argument to a another Excel function.
To quickly and easily get a better understanding of this Formula, you would select the cell housing it, then click the Function icon, left of the Formula bar. You can then study the individual argument of the VLOOKUP. To move into the MATCH function, simply click on the word MATCH is the Formula bar. Note also that Excel will bold the arguments of the Function (in the Formula bar) you are viewing.
You will also note that while in the Function argument dialog, it has "=" directly below the last argument of the Function being viewed. There is then a "Function =" at the very bottom. It is important know the difference with these.
The "=" will always show the result of the Function being viewed.
The "Function =" will always show the whole Function result.
If the Formula is not a nested one, both these will be the same.
In April we uploaded to our site a free open source Excel Add-in containing lot's of custom functions.DOWNLOAD FROM HERE
This month I will again show how to use a few of these and explain the code that drives them.
**EXTRACT_HYPERLINK**
Very simple to use function that will extract the URL or Email address from a Hyperlink. Used like; =Extract_Hyperlink(A1) where A1 has a Hyperlink.
*CODE*Function Extract_Hyperlink(HyperlinkCell As Range)
Extract_Hyperlink = Replace _
(HyperlinkCell.Hyperlinks(1).Address, "mailto:", "")
End Function
EXPLANATION
The code simply uses the VB Replace function to replace the word "mailto:" with emptytext("") within the Address (URL or Email address) of the Hyperlink. If there there is no "mailto:" it is simply ignored.
**EXTRACT_LAST_WORD**
Used to extract the last word in a cell housing text. Used like; Extract_Last_Word(A1) with cell A1 housing some text.
*CODE*
Function Extract_Last_Word(The_Text As String)
Dim stGotIt As String, i As Integer
i = 1
Do Until stGotIt Like (" *")
stGotIt = Right(The_Text, i)
i = i + 1
Loop
Extract_Last_Word = Trim(stGotIt)
End Function
EXPLANATION
The code uses a Do Until loop to loop until the String variable (stGotIt) has a space character followed by some text. It makes use of the Right Function to return the right most character(s) (number of characters to return is set by i) of Function argumnet The_Text (cell housing the text). At each loop the Integer variable (i) is incremented by one. So, if we used cell A1 and the text in this cell was; "Hello my name is David" the variable stGotIt would go like this
The Sixth loop would be the last as the Do Until condition has been met. That is stGotIt Like (" *"), where * is a wildcard representing any string of characters.
The final step is; Extract_Last_Word = Trim(stGotIt) which simply removes the leading space.
**EXTRACT_NUMBERS**
Used to extract all numbers from a cell housing both text and numbers. Used like; Extract_Numbers(A1) where A1 houses the text "There were 358 Dollars deposited". The number returned would be 358.
*CODE*
Function Extract_Numbers(rCell As Range)
Dim iCount As Integer, i As Integer
Dim sText As String
Dim lNum As String
sText = rCell
For iCount = Len(sText) To 1 Step -1
If IsNumeric(Mid(sText, iCount, 1)) Then
i = i + 1
lNum = Mid(sText, iCount, 1) & lNum
End If
If i = 1 Then lNum = CInt(Mid(lNum, 1, 1))
Next iCount
Extract_Numbers = lNum + 0
End Function
EXPLANATION
The text being stored with rCell (Function Argument) is passed to sText (String variable).
A For loop is then used to loop backwards (Step -1) Starting from the number of characters contained within sText (Len(sText)) and ending at 1.
An If statement is used to determine if Mid(sText, iCount, 1) is a number (IsNumeric). iCount is set to the number of characters within the text and steps backwards towards 1 at each loop. The Mid Function without variables would look like; Mid("There were 358 Dollars deposited", 32, 1) on the fisrt loop and return False to the If Statement as Mid would return "d" the from the word "deposited". The number 1 (used in Mid) tells Mid only to pull out a single character.
The If would keep returning False until theMid function returns 8 from 358. It would then drop to the next line and pass the result of Mid(sText, iCount, 1) to the String variable lNum. It also joins on the last number passed to lNum via use of & lNum. So, on the next loop the number 58 would be passed followed by 358. In this case all other loops would encounter False at; If IsNumeric(Mid(sText, iCount, 1)) Then.
The final step after all looping is; Extract_Numbers = lNum + 0. The reason for lNum + 0 is to force lNum to pass a true number to Extract_Numbers as apposed to text.
Until next month, keep Excelling!
ADVERTISEMENTS
Artificial neural network software for stock markets!
MAIN SOFTWARE CATEGORIES