If you cannot read HTML in your email please go here:

OzGrid Excel and VBA Newsletter

Add to GoogleSearch Tips Excel Data Manipulation and Analysis

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

MAIN INDEX

| What's New at OzGrid | Excel Tips and Tricks | Excel VBA Tips and Tricks | 50% Off Special |

What's New at OzGrid

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 :

10 Day 50% Off Super Special

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 toforecaster@ozgrid.com 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 forecaster@ozgrid.com .  If you would like to purchase you must do so by  21 August 2003.

Excel Tips and Tricks

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

  1. Activate Sheet1
  2. Select the range A1:A10
  3. Click in the Name Box. This is to the left of the Formula Bar below File Edit on the Worksheet Menu Bar.
  4. Type Sheet1!MyRange
  5. Push Enter
  6. Do the same for Sheet2 and 3 using Sheet2!MyRange and Sheet3!MyRange

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.

  1. Select cell A11 on any Worksheet
  2. Go to Insert>Name>Define
  3. In the Name Box: type: MyNumbers
  4. In the Refers to: box type =A$1:A$10
  5. Click Add then Ok.
  6. Now enter the number 1 in cell A1
  7. Select cell A1 and hold down the left mouse button over the Fill Handle - small black square bottom right of selection.
  8. Hold down the Ctrl key and drag down to cell A10
  9. Enter 1 in cell B1
  10. Drag down to cell B10 without holding down the Ctrl key.
  11. In A11 enter the formula =SUM(MyNumbers)
  12. In B11 enter the formula =SUM(MyNumbers)

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

  1. Select cell A11 on any Worksheet
  2. Go to Insert>Name>Define
  3. In the Name Box: type: MySum
  4. In the Refers to: box type =SUM(A$1:A$10)
  5. Click Add then Ok.
  6. Now enter the number 1 in cell A1
  7. Select cell A1 and hold down the left mouse button over the Fill Handle - small black square bottom right of selection.
  8. Hold down the Ctrl key and drag down to cell A10
  9. Enter 1 in cell B1
  10. Drag down to cell B10 without holding down the Ctrl key.
  11. In A11 enter the formula =MySum
  12. In B11 enter the formula =MySum

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.

 

Excel VBA Tips and Tricks

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.

Sort Cells by colors in Excel

Does an Excel Workbook Exist

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 :