Microsoft and Microsoft Excel are registered trademarks of Microsoft Corporation.

**OzGrid's Free Excel Newsletter**

Microsoft Excel and VBA for Excel Training Information (auto response)

The newsletter is divided into four sections:

1. What's new at OzGrid.com

2. Microsoft Excel tips

3. Microsoft Excel VBA tips

4. Helpful information

It is published on about the 10^{th }of each month and always written by myself Dave Hawley.

**You are more than welcome to pass on this newsletter to as many people as you wish, all I ask is you pass it on in it's entirety**.

Should you wish to no longer receive our newsletter, send an email with the words "Leave Newsletter" as the subject field, or click here.

Contained at the bottom of each newsletter is **Helpful Information** on how to get the most from our newsletters. If you think there is something missing, please let me know.

Microsoft and Microsoft Excel are registered trademarks of Microsoft Corporation

**10% off all Add-ins here! http://www.mbrm.com/ just tell them that you are a subscriber to the OzGrid newsletter and you will get 10% off!**

**What's new at OzGrid.com**

Well as this is the last Newsletter for the year 2001 and Christmas will soon be upon us, I wishto take the opportunity to wish you and your families a Merry Christmas andHappy New Year!

We have finally now got full control over both our Domains (www.MicrosoftExcelTraining.com and www.OzGrid.com) either one should now take you to our Web site! I have also set up some automatic redirect pages for the individual pages on www.MicrosoftExcelTraining.com/ However, should you come across one somewhere out there on the WWW, please let me know and I'll set one up for it.

A big thanks to all of those that sent an email with answers to my short list of questions last week. From the responses I obtained it looks like www.Google.com is the preferred search engine for OzGrid newsletter subscribers. To be honest, it's also my preferred search engine! For those of you that don't use Google, I can highly recommend it for finding what you want.

Last months offer of 20% off any training was certainly a big hit with all of those that are looking for some quality Excel training. For all of those that have paid, but wish to commence early next year I look forward to sharing my knowledge with you. Just remember I am only too happy to answer all your Excel questions!

**Microsoft Excel tips**

__A couple of handy uses for the INDIRECT function__

For this months tips, I thought we would look at how the **INDIRECT **function can be used in "out of the box" ways. Let's assume you have a simple **SUM **function being used to sum a range of numbers (A1:A100). It works just fine until a row is inserted or deleted! If you delete a row it ends up reading: **=SUM($A$1:$A$99) **or if you insert a row it reads: **=SUM($A$1:$A$101)**. This is no problem of course if this what you want and expect, but it would seem (from the amount questions) it's not what is wanted! There is a very simple way this can be overcome and that is with the aid of the INDIRECT function: **=SUM(INDIRECT("$A$1:$A$100"))**. This way Excel will read the same range no matter how many rows are added or deleted. For those who are not familiar with the INDIRECT function it allows Excel to see a text string as a range address.

Another common problem among Excel users is **not being able **to use the **List **option of the **Validation **feature to refer to another Worksheet. To see what I mean try this:

- Type the numbers 1 to 10 in A1:A10
- On any other Worksheet select any cell and go to
**Data**>**Validation**. - Select "
**List**" from the**Allow:**box - Click the
**collapse dialog button**to the right of the "**source:"**box - Click the Worksheet tab of the sheet containing your numeric list (ie. A1:A10)

Cannot be done can it! Even if you type **=Sheet1!A1:A10** and then click OK, Excel tells you that you cannot refer to another Worksheet. That's what Excel thinks, but this can be overcome.

Instead of typing **=Sheet1!A1:A10** type: **=INDIRECT("Sheet1!A1:A10")** and Excel quite happily accepts it.

Not familiar with what can be done with Validation? try here: http://www.ozgrid.com/Excel/Formulas.htm

**Stop users scrolling about**

Another common questions I have been receiving lately is how can I **stop **the average Excel user from scrolling about my worksheet, without using VBA. *I say average Excel user as this method (unless combined with sheet protection) can be overcome!* All you need to do is hide all unused rows and columns. The easiest way to do this is to select the **entire row **below you last row of data, then holding down your **Ctrl **and **Shift **key push the **Down Arrow**. This will select all your unused rows, now right click and select **Hide**. Do the same for you Columns expect obviously switch **entire row,** **last row** and **Down Arrow **with **entire column, last column **and **Right Arrow**.

Once you have done the above you may need to place or change some data in a cell that is not hidden. The quickest way to do this is either type the address in the **Name Box **(left of the formula bar) and push **Enter**, or push **F5 **and type the cell address in the **Reference: **box and click **OK**. Now just type as normal!

__Quickly remove unwanted rows/columns or cells__

Imagine you have a large table of data and you want to delete all rows in the table that have a blank cell in column "A". Here's a quick method you can use:

- Select the entire column "A"
- Push
**F5**and click**Special** - Check
**Blanks**and click**OK** - Go to
**Edit**>**Delete**and make your choice.

Another very good method is to simply sort your data by the column containing the blank cells. Of course this may not be suitable in all circumstances.

**Microsoft Excel VBA tips**

Before we look at some VBA tips this month I would like to direct you all to a handy little Download I wrote and posted here: http://www.ozgrid.com/download/default.htm called **PrintCol.zip.**

This month I thought I would show you the ConvertFormula method! This method allows us to change a range of formulas from Absolute to Relative, Relative to Absolute, Relative Row to Absolute Row and so on... In fact we can change any relative or absolute aspect of any formula. The Excel help for the ConvertFormula is written as below:

Converts cell references in a formula between the A1 and R1C1 reference styles, between relative and absolute references, or both.

*expression***.ConvertFormula( Formula**,

*expression* Required. An expression that returns an **Application** object.

** Formula** Required

** FromReferenceStyle** Required

** ToReferenceStyle** Optional

** ToAbsolute** Optional

** RelativeTo** Optional

So with the above in mind we can easily write a simple Procedure that will change our formulas for us

'www.ozgrid.com

Dim RdoRange As Range

Dim i As Integer

Dim Reply As String

'Ask whether Relative or Absolute

Reply = InputBox("Change formulas to?" & Chr(13) & Chr(13) _

& "Relative row/Absolute column = 1" & Chr(13) _

& "Absolute row/Relative column = 2" & Chr(13) _

& "Absolute all = 3" & Chr(13) _

& "Relative all = 4", "OzGrid Business Applications")

Reply = InputBox("Change formulas to?" & Chr(13) & Chr(13) _

& "Relative row/Absolute column = 1" & Chr(13) _

& "Absolute row/Relative column = 2" & Chr(13) _

& "Absolute all = 3" & Chr(13) _

& "Relative all = 4", "OzGrid Business Applications")

'They cancelled

If Reply = "" Then Exit Sub

On Error Resume Next

'Set Range variable to formula cells only

Set RdoRange = Selection.SpecialCells(Type:=xlFormulas)

If Reply = "" Then Exit Sub

On Error Resume Next

'Set Range variable to formula cells only

Set RdoRange = Selection.SpecialCells(Type:=xlFormulas)

'determine the change type

Select Case Reply

Case 1 'Relative row/Absolute column

For i = 1 To RdoRange.Areas.Count

RdoRange.Areas(i).Formula = _

Application.ConvertFormula _

(Formula:=RdoRange.Areas(i).Formula, _

FromReferenceStyle:=xlA1, _

ToReferenceStyle:=xlA1, ToAbsolute:=xlRelRowAbsColumn)

Next i

Case 2 'Absolute row/Relative column

For i = 1 To RdoRange.Areas.Count

RdoRange.Areas(i).Formula = _

Application.ConvertFormula _

(Formula:=RdoRange.Areas(i).Formula, _

FromReferenceStyle:=xlA1, _

ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsRowRelColumn)

Next i

Case 3 'Absolute all

For i = 1 To RdoRange.Areas.Count

RdoRange.Areas(i).Formula = _

Application.ConvertFormula _

(Formula:=RdoRange.Areas(i).Formula, _

FromReferenceStyle:=xlA1, _

ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsolute)

Next i

Case 4 'Relative all

For i = 1 To RdoRange.Areas.Count

RdoRange.Areas(i).Formula = _

Application.ConvertFormula _

(Formula:=RdoRange.Areas(i).Formula, _

FromReferenceStyle:=xlA1, _

ToReferenceStyle:=xlA1, ToAbsolute:=xlRelative)

Next i

CaseElse 'Typo

MsgBox "Changetype not recognised!", vbCritical, _

"OzGrid Business Applications"

End Select

'Clear memory

Set RdoRange = Nothing

**End Sub**

Select Case Reply

Case 1 'Relative row/Absolute column

For i = 1 To RdoRange.Areas.Count

RdoRange.Areas(i).Formula = _

Application.ConvertFormula _

(Formula:=RdoRange.Areas(i).Formula, _

FromReferenceStyle:=xlA1, _

ToReferenceStyle:=xlA1, ToAbsolute:=xlRelRowAbsColumn)

Next i

Case 2 'Absolute row/Relative column

For i = 1 To RdoRange.Areas.Count

RdoRange.Areas(i).Formula = _

Application.ConvertFormula _

(Formula:=RdoRange.Areas(i).Formula, _

FromReferenceStyle:=xlA1, _

ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsRowRelColumn)

Next i

Case 3 'Absolute all

For i = 1 To RdoRange.Areas.Count

RdoRange.Areas(i).Formula = _

Application.ConvertFormula _

(Formula:=RdoRange.Areas(i).Formula, _

FromReferenceStyle:=xlA1, _

ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsolute)

Next i

Case 4 'Relative all

For i = 1 To RdoRange.Areas.Count

RdoRange.Areas(i).Formula = _

Application.ConvertFormula _

(Formula:=RdoRange.Areas(i).Formula, _

FromReferenceStyle:=xlA1, _

ToReferenceStyle:=xlA1, ToAbsolute:=xlRelative)

Next i

CaseElse 'Typo

MsgBox "Changetype not recognised!", vbCritical, _

"OzGrid Business Applications"

End Select

'Clear memory

Set RdoRange = Nothing

The important part to note in the above Procedure is that we do not loop through all cells in the selection looking for formulas, we simply set a range variable to the SpecialCells method using xlFormulas as the Type argument. This way we are not:

- Looping through potentially thousands of cells we are not interested in.
- Forcing the user to select only formula cells.

I hope you can all find a use for this one, I know I certainly can and do!

Until next month, keep Excelling!

Kind regards

Dave Hawley

**You are more than welcome to pass on this newsletter to as many people as you wish, all I ask is you pass it on in it's entirety**.

**Helpful Information**

Microsoft and Microsoft Excel are registered trademarks of Microsoft Corporation.