What's New at OzGrid | Excel Tips and Tricks | Excel VBA Tips and Tricks
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!
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 .........
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;
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;
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;
**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
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
Download a working example from here:
Until next month, keep Excelling!
Artificial neural network software for stock markets!
EXCEL TEMPLATES SPECIALS
MAIN SOFTWARE CATEGORIES
|Microsoft Excel Add-ins||Financial Software|
|Microsoft Excel Training & Tutoring||File Conversion Software|
|Microsoft Excel Templates||Construction and Estimating Software|
|Excel, Word, Access Password Recovery||Financial Calculators|
|Corrupt Excel, Word, Access File Recovery||Real Estate Investment Analysis Software|
|Time & Project Management Software||Excel on the WWW|
|Windows & Internet Software||Database Software|
|Barcode, Fonts, ActiveX, labels and DLL's||OUR CURRENT SOFTWARE SPECIALS!|
|Charting Software||The Analysis Add-ins Collection|
|Trading Software||TOTALLY FREE 24/7 EXCEL, VBA SUPPORT!|