OzGrid Excel and VBA Newsletter

Search Tips

Newsletter Index

Search Tips

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

What's New at OzGrid

Download the Free Ozgrid Toolbar

We are very excited about our new affiliation with the one and only company ever chosen by Microsoft® to include their Templates inside Microsoft Excel®. Below are the URL's of these Templates. As with most products Ozgrid offers, ALL have a Free 30 Day Money Back Guarantee!

1. Analytical Spreadsheets
2. Business Plans FastPlan
3. Business Trouble-shooter
4. Financial Forecasting Ventures
5. Financial Ratios
6. Human Resource Pack
7. Lease Vs Purchase
8. Litigation Budget
9. Marketing & Sales Pack
10. Net Worth Builder
11. Office Analytical Pack
12. Personal Finance Pack
13. Personal Finance Pack II
14. Retirement Planner
15. Sales & Marketing Pack

Well, last month we received the first lot of stats on our book, Excel Hacks - 100 Industrial Strength Tips and Tools , which were very good. This month, the book was released in Japan and so far seems to be doing very well there also. At the end of October we also received our first Royalty cheque. I was going to frame it and put in on the wall in our office, but decided I could put the money to a much better use in other areas!!!

We have Start ed updating our Level 3 course this month, we are more than half way through updating the lessons now and can see the light at the end of the tunnel. After the Level 3 lessons, Dave will Start updating the VBA for Excel lessons.

All aspects of the business are doing well, with a record number of visitors to our website.

Until next month .........

Excel Tips and Tricks

This month I will show you some handy formulas used with a twist.

SUM ALL SHEETS

The first one is how we can use the SUM function to Sum, say cell A1, on all Worksheets in your Workbook. With this method, all new sheets that are added to the Workbook are included in the SUM. Here is how;

1. Add a new Worksheet to you Workbook at the far right. To do this select "Worksheet" under "Insert" on the Worksheet Menu Bar. Then left click on this new Worksheet name tab, holding down the the left mouse button drag to the far right and release.
2. Name this sheet (double click the name tab) "Spacer" (although any name will do).
3. With "Spacer" being the active sheet, go to Format>Sheet>Hide.
4. Repeat steps 1 to 3 above, but this time name the Worksheet "Start " and drag it to the far left.
5. On the Worksheet you wish to have the result shown on enter: =SUM('Start :Spacer'!A1)

Add any number of new Worksheets, enter some numbers into A1 of any Worksheet and it will be included in the SUM. Move the Worksheets order around, Delete sheets etc and the SUM function still includes all Worksheets.

If you have concerns that a user may unhide/delete/move the Worksheets "Start & "Spacer", go to Tools>Protection>Protect Workbook (ensure "Structure" is checked), supply a password (optional) and click OK.

PREVENT BLANKS IN TABLES

For Excel to be used to its full potential data entry should be done in a classic table format. That is, headings across row one of the Table and data in continuous cells directly underneath the appropriate heading. See:The Number One Mistake Made by Spreadsheet Users for details.

If you do have blanks in a list of data (where the blanks represent the data above) you can easily fill these is like shown below;

FILL BLANKS

Let's say you have a list of entries in column A and within the list you have many blank cells. Here is a quick way to fill those blanks with the value of the cell above. Highlight column A, then push F5 and click Special then check the Blanks option and click OK. Now push Equals (=) then the Up arrow and finally holding down the Ctrl key push Enter.

Or, you can download a free Add-in from us here

Ok, as prevention is better than cure, here is how we can avoid this situation in the first place. For this example, the range A1:B100 (A1 and B1 are headings) will be our table where we enter Names (A2:A100) and Departments (B2:B100).

SelectA3:B100 and ensure your selection Start s from cell A3. Now go toData>Validation and choose the "Custom" from "Allow" and then in the "Formula" box add the formula below;

=AND(COUNTA($A$2:$A2)=COUNTA($B$2:$B2),COUNTBLANK($A$2:$A2)=COUNTBLANK($B$2:$B2))

**It is very important to note the Absolution of the $A$2 and $B$2 and the Relative Row/ Absolute Column of $A2 and $B2**

Select the "Error Alert" page and type an applicable error message the user will see if they leave blanks in the Table. Ensure the "Error style" is set to "Stop" and click OK.

The Validation applied will ensure that all entries (in the Table A2:B100) have both a name and a department.

To see this in action, download the Workbook example here

Excel VBA Tips and Tricks

In keeping with the use of Validation via the "Data" menu (shown above in the Excel Tips and Tricks) let's look at how we can enhance this nifty feature.

When we use Validation with the "List" option we can have our users select any item from the list. However, to be able to add new entries to the list we first must add the item to the referenced range and then choose it from list in the validated cell.

Below is a method I have used to have new items added to the list simply by entering them in the validated cell. A message is then shown which asks the user if this new item should be added, or not. If they choose "Yes", the new item becomes part of the list.

UPDATING VALIDATION LIST

For this example the cell with the Validation list will be A13 and E12 downwards will be where our referenced list resides. This list will be Named "Names" and will be aDynamic Named Range.

The formula used for the Dynamic Named Range "Names" will be;=OFFSET('Updating Validation List'!$E$12,0,0,COUNTA('Updating Validation List'!$E$12:$E$10000),1)

Below is the code which is used to determine when a new entry is added in cell A13. It MUST reside in the Private Module of the Sheet Object. To quickly get there, right click on the sheet name tab and select "View Code". Although the cell A13 has Validation applied the "Show error alert when invalid data entered" is not checked. This is so the VBA code can do the validating.Download a working example from here:

Dim strOriginalEntry As String
Private Sub Worksheet_Change(ByVal Target As Range)
Dim iReply As Integer
    If Target.Cells.Count > 1 Then Exit Sub
    ' Change to Validated cell
    If Target.Address = "$A$13" And Target <> vbNullString Then

    'New Name
        If WorksheetFunction.CountIf(Range("Names"), Target) = 0 Then
            'Prevent Change Event Firing again while code is running.
             Application.EnableEvents = False
            'Ask if they wish to add the name or not.
            iReply = MsgBox("The name " & Target & _
            " is not part of the list, do you wish to add it.", _
            vbYesNoCancel + vbQuestion, "ozgrid.com")
                If iReply = vbCancel Then 'Cancelled so restore original text
                    Target = strOriginalEntry
                ElseIf iReply = vbNo Then
                   'Don't add to list. That is do nothing
                Else 'Add the new name to the cell below _
                        the last name in the named range "Names"
                    Range("Names").Cells(1, 1).End(xlDown)(2, 1) = Target
                End If
        End If
    End If
    Application.EnableEvents = True 'Allow Events
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Pass orginal Text to the Module level Variable "strOriginalEntry"
If Target.Address = "$A$13" Then strOriginalEntry = Target
End Sub

Download a working example from here:

Until next month, keep Excelling!

ADVERTISEMENTS

Artificial neural network software for stock markets!

EXCEL TEMPLATES SPECIALS

DATABASE SOFTWARE

MAIN SOFTWARE CATEGORIES