If you cannot read HTML in your email please go here: http://www.ozgrid.com/News/Archive.htm

OzGrid Excel and VBA Newsletter

Search Tips

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 ofMicrosoft Corporation Read this issue and past issuesonline here :

**PLEASE DO NOT REPLY TO THIS ADDRESS **

All Excel Templates Normally an 80% saving at $299.00. November Super Special $127.00! Best Value on the WWW More Hot Specials!

We Recommend SmartDraw for Flowcharts

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

Excel on the WWW

MAIN INDEX

What's New at OzGrid | Excel Tips and Tricks | Excel VBA Tips and Tricks

What's New at OzGrid

That's it for this month, enjoy the newsletter and remember tocheck out any past issues here

As most of you are by now aware we have been very busy writing the upcoming Book for O'Reilly Press; 100 Excel Hacks. We are now just past the half-way mark and myself and my wife Raina are still talking!

In addition to writing the book I have been searching the Web for good products that I think will be of use to our site visitors. I have added too many to list here so I would suggest going here:http://www.ozgrid.com/Services/ExternalAddIns.htm As an added bonus to ALL newsletter members not only will you get a free copy of our33 Workbook Downloads when making any purchase, you will also get a free copy of Formula Reference Changer & Sheet Index Creator valued normally at $25.00

Why Buy From OzGrid ?

1. Security: We use one of the most trusted names in online transactions, Regnow. Read Regnow Privacy Statement!
2. Money Back Guarantee:We offer a Full 30 Day Unconditional Money Back Guarantee on most products.
3. Fair Price: We offer such a wide range of products that you can shop around within our site and compare prices for similar products.
4. Free Give-ways: All customer are entitled to a free copy of our33 Workbook Downloads and now all newsletter members also get a free copy of Formula Reference Changer & Sheet Index Creator

Free Excel, Word, Access, PowerPoint and E-mail Support

OurOzGrid Forum continues to grow in popularity each day. If you haven't already given yourself and/or your company totally FREE 24/7 support,simply register here ALL forum members have the opportunity to earn some extra money.Simplygo here to find out how Where else do you have the opportunity to earn $$ while answering, or asking question?

Excel Tips and Tricks

Create spreadsheets like a pro. Subscribe to Working Smarter with Microsoft Excel Affiliate SiteBarcode, Fonts, ActiveX, DLL's, Labels and more!

One common problem often encountered when developing and/or modifying spreadsheets is that there are often times when you may like to reference a single Cell such as Cell A1 and then copy this reference across columns to the right.  Naturally, this results in the Formula reference changing to =B1, =C1, =D1 etc.  However, this is not the result you want.  Wouldn’t it be nice if Excel had a function or option that allowed us to determine whether our Cell references should increment by rows or columns regardless of if we copy across or down.  Unfortunately, there is no such option in Excel, and I haven no idea of whether there is any intention of putting one in. However, we can do this by using a combination of the INDIRECT Function with the ADDRESS Function nested within.

Perhaps the best way to explain how to create the Function needed is to use a logical example.  In the Cells A1:A10 enter the numbers 1 – 10 in numerical order.  Go to Cell D1 and in this Cell enter:

=INDIRECT(ADDRESS(COLUMN()-3,1))

As soon as you enter this you should have the number 1 showing in Cell D1.  This is because our Formula is actually referencing Cell A1.

If you now copy this Formula across some columns to the right, you will notice that Cell E1 will contain 2, F1 will contain 3 etc.  In other words, although we are copying across columns, our Formula reference is incrementing by rows. 

I find this method very useful especially when I have a spreadsheet which may have headings going down rows in one particular column and I wish to create a dynamic reference to these row headings across columns.

While this is a fairly straight forward process if we are only referencing a single Cell, there are often times when you may need to do something along the same lines except the range will not be a single Cell, it will be a range of Cells and that range of Cells is being used in the argument for a function. 

Lets use the all popular SUM function to demonstrate what I mean.

Assume you have been given a huge long list of numbers and your job is to not sum all of the numbers, but to sum the column of numbers in a running total fashion like this; =SUM($A$1:$A$2), =SUM($A$1:$A$3), =SUM($A$1:$A$4).  The problem comes about because the results need to be dynamic and they also need to span across 100 columns on row 1 only and not down 100 rows in another column (as would often be the case).

Naturally, we could manually type such functions into each individual Cell, but as you can imagine this would take a lot of unnecessary time.  Here is how we can do it using the same principle as we did when referencing a single Cell.

Again, fill the range A1:A100 with the numbers 1 – 100 in their numeric order

 Go to Cell D1 and enter this Formula:=SUM(INDIRECT(ADDRESS(1,1)&”:”&ADDRESS(COLUMN()-2,1)))

This will then give you the result of 3, which is the sum of the Cells A1:A2.  Copy this Formula across to Cell E1 and you will get the result of 6, which is the sum of A1:A3.  Copy to Cell F1 and you will get the result of 10, which is the sum of Cells A1:A4.

It is the use of the volatile function COLUMN that causes our last Cell reference to increment by 1 each time we copy it across to a new column.  This is because the COLUMN Function always returns the column number (not letter) of the Cell that houses it.

Excel, Word, Access Password Recovery ||Corrupt Excel, Word, Access File Recovery

Excel VBA Tips and Tricks

Are You Into Excel and Excel VBA?Our 4 Most Popular Bundled Savings

If you have ever had to work in a Workbook that has many Worksheets, you will know how painful it can be to locate a specific Worksheet.  An index sheet that we could easily access from any Worksheet would make navigation much easier.  Here are two ways to create one.

The first way simply entails inserting a Worksheet, calling it Index or something similar and on this Worksheet entering a list of all your Worksheet names and then creating a hyperlink for each of these names to the desired Worksheet, that is; right click>Hyperlink.  While this method is probably sufficient in some instances, you will note that if and when you add/delete Worksheets your index is no longer valid.  We can overcome this by placing some VBA code into the Private Module of the Worksheet like this:

Private Sub Worksheet_Activate()
Dim wSheet As Worksheet
Dim l As Long
l = 1
    With Me
        .Columns(1).ClearContents
        .Cells(1, 1) = "INDEX"
        .Cells(1, 1).Name = "Index"
    End With
   
    For Each wSheet In Worksheets
     If wSheet.Name <> Me.Name Then
      l = l + 1
      With wSheet
          .Range("A1").Name = "Start " & wSheet.Index
          .Hyperlinks.Add Anchor:=.Range("A1"), Address:="", SubAddress:= _
            "Index", TextToDisplay:="Back to Index"
      End With
         Me.Hyperlinks.Add Anchor:=Me.Cells(l, 1), Address:="", SubAddress:= _
            "Start " & wSheet.Index, TextToDisplay:=wSheet.Name
     End If
    Next wSheet
End Sub

To insert this code into your Workbook, it must reside within the Private Module for the Sheet Object.  

1. Insert a new Worksheet and name it something appropriate like Index Sheet.
2. To quickly get to the Private Module, simply right click on the Sheet Name tab and select View Code.  This is where you should place the above code.
3. When finished, you can either click the X in the top right hand corner, or push Alt + Q to get back to the Excel interface.

It is important to note that the code above, written as is, names Range A1 on each sheet Start followed by a number that represents the Sheet Index Number.  This ensures that Range A1 on each sheet has a different name.  If Range A1 on your Worksheet already has a name, you should consider changing all occurrences of .Range("A1") to a suitable range address.

The other method that can be used, which is probably a lot more user-friendly, is to manipulate the pop-up menu displayed when you right click on any Cell in any Worksheet.  To this we can add our own menu item which when clicked will show the standard Workbook Tabs Command Bar.  If you are not familiar with this Command Bar, you can see it by right-clicking on your Sheet Scroll Tab arrows on the bottom left of any Worksheet.

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Dim cCont As CommandBarButton
    On Error Resume Next
    Application.CommandBars("Cell").Controls("Sheet Index").Delete
    On Error GoTo 0
        Set cCont = Application.CommandBars("Cell").Controls.Add _
                        (Type:=msoControlButton, Temporary:=True)
        cCont.Caption = "Sheet Index"
        cCont.OnAction = "IndexCode"
End Sub

To insert the code as shown above:

1. Open the your Workbook and then right click on the Excel icon immediately to the left of the File Item on the Worksheet Menu Bar and select View Code.  Now enter the code exactly as shown above
2. Go to Insert>Module to insert a Standard Module and enter in the following code.  This macro (IndexCode) is called via the code we entered in Step 1 whenever a user right clicks on any Worksheet. Sub IndexCode()
    Application.CommandBars("Workbook Tabs").ShowPopup
   End Sub
3. When finished, you can either click the X in the top right hand corner, or push Alt + Q to get back to the Excel interface
4. Now right click on any Worksheet and you should see a new menu item added to the Shortcut menu named Sheet Index.

Until next month, keep Excelling!

Office Ready Professional 3.0 |High Impact e-Mail 2.0 |Office-Ready Business Plans |Office-Ready Marketing Plans |e-Marketing Suite |Office Policy Manual |Ultimate Everyday Calculator |Ultimate Financial Calculator |Ultimate Marketing Calculator |Template Zone Home Page |Office Ready Stuff It |Ultimate Loan Calculator

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 :