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 10th 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:
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:
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, FromReferenceStyle, ToReferenceStyle, ToAbsolute, RelativeTo)
expression Required. An expression that returns an Application object.
Formula Required Variant. A string that contains the formula you want to convert. This must be a valid formula, and it must begin with an equal sign.
FromReferenceStyle Required Long. The reference style of the formula. Can be one of the following XLReferenceStyle constants: xlA1 or xlR1C1.
ToReferenceStyle Optional Variant. The reference style you want returned. Can be one of the following XLReferenceStyle constants: xlA1 or xlR1C1. If this argument is omitted, the reference style isn't changed; the formula stays in the style specified by FromReferenceStyle.
ToAbsolute Optional Variant. Specifies the converted reference type. Can be one of the following XLReferenceType constants: xlAbsolute, xlAbsRowRelColumn, xlRelRowAbsColumn, or xlRelative. If this argument is omitted, the reference type isn't changed.
RelativeTo Optional Variant. A Range object that contains one cell. Relative references relate to this cell.
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.