If you cannot read HTML in your email please go here: If you cannot read HTML in your email please go here:
You are more than welcome to pass on this newsletter to as many people as you wish. Or even upload it (as is) to your Web site!
To no longer receive our newsletter, send an NEW email with the exact words "action: Unsubscribe" in the body of the email, or click here .
Microsoft and Microsoft Excel are registered trademarks of Microsoft Corporation
Read this issue and past issues online here :
PLEASE DO NOT REPLY TO THIS ADDRESS
| The Excel Add-ins Shop . The Excel Template Store . The Largest Collection Around All With a 30 Day Money Back Guarantee!The Software Store For all Business and Financial Related Software.Learn Excel From Those That Develop it, OzGrid . We teach from experience not a manual! Downloadable, Online or E-mail for all levels. Big Specials on all our Downloadable Excel Training FREE 24/7 Support For You, Or Your Business: OzGrid Excel Forum An information superstore |
| What's New at OzGrid | Excel Tips and Tricks | Excel VBA Tips and Tricks | 50% Off Special |
Another month has come and gone and has been a busy one for us and our Australian clients especially. It was the end of the Financial year on 30 June here in Australia, which made the first two months of July reasonably quiet business-wise. We took advantage of the "breather" to get our tax in order and to investigate some exciting new projects and finish up some on-going ones.
One of the projects that we have been involved in is to contribute an article to a market research book written by a Professor at the University of West Florida. The book is widely used by both students and industry in the US. We have written a couple of paragraphs about how Excel can be used for data analysis suitable for marketing research projects and how companies can use Excels power, flexibility and usefulness re marketing research applications. We will keep you informed and let you know when it is due to be published.
Things certainly became hectic again around the middle of July and have continued that way leading into August. The Forum is going exceptionally well and we have had lots of positive feedback on the updates we have made to the website. Enjoy the newsletter and remember to check out any past issues here :
This month we have another half price offer for ALL newsletter members. The product is theForecaster Excel add-in based on neural networks. It is the first choice for Excel users who need a reliable and quick-to-learn forecasting tool embedded into familiar Excel interface.
The normal price of this product is $149.95 but for the next 10 days (offer ends 21 August 2003) for all newsletter members the price is only $74.95. If you would like to take up this offer, simply send an email to[email protected] and you will receive payment instructions via a reply email.
IMPORTANT: Do not use the Buy Now buttons on theForecaster Excel add-in pages to make your payment, instead send an email to [email protected] . If you would like to purchase you must do so by 21 August 2003.
In Issue 14: Jun 2002 we looked at named ranges in some detail. One area we did not cover was the use of sheet level named ranges in Excel.
Normally when you name a range the name is at the Workbook level, meaning the name refers to a specified range on a specified Worksheet. Once the name has been used it cannot be used again to represent a range on another Worksheet. However, sometimes it can be very handy to have one name that will refer to a specified range on the active sheet at the time. Here is how it is done!
Assume we have a Workbook with 3 Worksheets. These 3 Worksheets are simply named Sheet1, Sheet2 and Sheet3. We want to have a named range called MyRange (can be any legitimate name) that will refer to the range Sheet1 A1:A10 when on Sheet1, Sheet2 A1:A10 when on Sheet2 and Sheet3 A1:A10 when on Sheet3. Here is how
Now activate any sheet and click the drop arrow on the Name Box. You should see only one occurrence of the name MyRange. Select this and you will be taken direct to the range A1:A10. Now activate any other sheet and do the same. You will always be taken to the range A1:A10 of the active sheet.
The reason we can do this is because we preceded the name with the sheet name followed by the ! (exclamation mark). If you go into Insert>Name>Define you will note that you only see one name and that name is the one that refers to the current active sheet.
If your Worksheet name includes spaces you cannot simply use Sheet1!MyRange. What you must use is'Sheet 1'!MyRange. In fact you can use the single apostrophes with a Worksheet name with no spaces. It is a good idea to always use the single apostrophes when referring to Worksheet name as it covers all bases.
Another little trick that can often come in handy is using a relative reference named range. By default, named ranges are absolute but we do not have to leave them this way. For example try this.
You should get 55 and 10 respectively. The reason for this is that we had cell A11 active when we went toInsert>Name>Define and referred our range name to A$1:A$10 which is a relative Column and absolute Row named range. It's the $ sign that forces any range to be absolute.
When we use the name MyNumbers in a formula, it will always refer to the 10 cells immediately above. If you used =SUM(MyNumbers) in cell A11 of another Worksheet it will still refer to cells A1:A10 on the sheet which was active when we originally created the range name.
Ok, lets take this one step further. Suppose we want to simplify the summing of the 10 cells above. Here is how
As you will see we get the same results now but without the need for the Sum function. Have a play about with these types of names, mix up the absolute and relative references and nest a few function together, they can be very handy and save a lot of work in some cases.
Adding Comments To Formulas
Sometimes when you write a formula it can be very handy to add some text to part of it so you can make easy reference to it later. For this we can use the little known about, or used, N Function for example
=SUM($A$1:$A$10)+N("Adding Daves Expenses")
It will have no adverse affect on the formula because the N Function returns zero for text.
What N Returns
A number: N Returns That number
A date, in one of the built-in date formats available in Microsoft Excel: N Returns The serial number of that date
TRUE: N Returns 1
FALSE: N Returns 0
An error value, such as #DIV/0!: N Returns The error value
Anything else: N Returns 0
The Function itself is made available in Excel for compatibility with other spreadsheet programs.
This month I thought I would include some of the most popular Custom Functions from our site. One very frequent question we are asked is: "How can I Sum cells by background color, font color or another type of format?" We have2 Custom Excel Functions that will both Sum and Count by cell color, but the need is often to be whether the cells font is bold or not and other factors. The easiest way to do this is via a UDF like the one shown below
Function FormatSum(rCriteriaCell As Range, rRange As Range)
Dim rcell As Range
Dim bMatch As Boolean
Dim vResult
For Each rcell In rRange
With rcell
bMatch = (rCriteriaCell.Interior.ColorIndex = _
.Interior.ColorIndex And _
rCriteriaCell.Font.ColorIndex = _
.Font.ColorIndex And _
rCriteriaCell.Font.Bold = _
.Font.Bold And _
rCriteriaCell.Font.Italic = _
.Font.Italic And _
rCriteriaCell.Font.Underline = .Font.Underline)
End With
If bMatch = True Then
vResult = WorksheetFunction.Sum(rcell) + vResult
End If
Next rcell
FormatSum = vResult
End Function
This Function can be used as shown below:
| A | |
| 1 | $ 10.00 |
| 2 | $25.00 |
| 3 | $99.00 |
| 4 | $ 25.00 |
=FormatSum($A$1,A1:A4)
Where $A$1 has the format conditions that must be met to be included in the SUM.
If you use a Custom Function like this (that loops through all cells), it is advised to NOT reference an entire Column and restrict the range to only those that contain data. If you don't you will really slow down your calculations.
It should also be noted that the changing of a cells format will NOT trigger a recalculation. Do NOT be tempted to make the function Volatile with the use of Application.Volatileas again any change to a cells format will not trigger a recalculation.
TIP: The easiest way to force a recalculation, when you change the format of any of the referenced cells (B1:B10 in this case), is to use the Format Painter on the Standard toolbar, or use Paste Special - Formats.
This UDF will return True Or False depending on whether the specified Excel Workbook exists or not.
Function DoesWorkBookExist(FilePath As String, _
Filename As String) As Boolean
With Application.FileSearch
.LookIn = FilePath
'* represents wildcard characters
.Filename = Filename
DoesWorkBookExist = .Execute > 0
End With
End Function
Enter the function like this.
=DoesWorkBookExist("C:/OzGrid Likom/Testings","Book2.xls")
OR
=IF(DoesWorkBookExist("C:/OzGrid Likom/Testings","Book2.xls"),
"Do True Statement","Else Do False Statement")
Where you replace Do True Statement with the Excel Function to perform if True and Else Do False Statement with the Excel Function to perform if False
Extract Hyperlink Address From Cell
This UDF will extract the underlying address from a cell containing a Hyperlink
Function GetAddress(HyperlinkCell As Range)
'If using Excel 97 use the WorksheetFunction.Substitute in place of Replace
GetAddress = Replace _
(HyperlinkCell.Hyperlinks(1).Address, "mailto:", "")
End Function
Use the Function in any cell as shown below.
=GetAddress(A1) Where cell A1 has a Hyperlink within it.
Until next month, keep excelling!
You are more than welcome to pass on this newsletter to as many people as you wish. Or even upload it (as is) to your Web site!
To no longer receive our newsletter, send a NEW email with the exact words "action: Unsubscribe" in the body of the email, or click here .
Microsoft and Microsoft Excel are registered trademarks of Microsoft Corporation
Read this issue and past issues online here :