All Excel Templates Super Special $189.00! New Templates Added!
We Recommend SmartDraw for Flowcharts NewsletterSubscriber Specials
 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 |
What's New at OzGrid | Excel Tips and Tricks | Excel VBA Tips and Tricks What's New at OzGrid
We have been so busy lately, we are only half way through updating our Level 2 training lessons. The Level 1 course is selling really well, so we are making a concerted effort to finish Level 2 this month. The Training courses are being offered in two modes, as discount downloadable training and now via our new Training Forum. Click here to see the details. Once the Training Lessons are completed, our next project will be to create an on-line exam for students to take after completion of the lessons, if they wish. This will probably be in the form of a multiple choice assessment, but we are not 100% sure yet.
If you are a Forum member you are able to take the training in our new Training Forum at a discount. Click here to do this and see our prices. Level 1 now available.
The stats forour book , still have not arrived, but we have been informed that sales are going very well. O'Rielly have done a few promotions with it, including one at Barnes & Noble where it sold about double the next-best Hacks book in the promotion.
We also have an association with Amazon and have set-up a store here All proceeds fro this store will be used to enhance the question Forum and keep it totally free. See the "Books, Software, CD's, DVD's etc" link near the top of all Forum pages. 
That's all for this month, we hope you enjoy the newsletter
Last month I showed a way to use the old Excel 4.0 EVALUATE Function to have the cell contents, shown below, to return their calculated results in their adjacent cells.
A1= 21+69+89+25+31
A2= 21*25
A3= 100/10
A4= 100/10*(10*10+10)
A5= 100/10*10*10+10
I stated that the use of = "="&A1 in cell B1 simply resulted in displaying: =21+69+89+25+31 and NOT evaluating the formula. This is when I showed how to use the EVALUATE Function. If you missed last month, you can read it here
Anyway, a newsletter subscriber (named Graham Gidney) emailed me and showed me yet another way. In fact, his way is even easier! All you need to do is enter = "="&A1 into B1, copy down to B5, then select B1:B5 and Copy, then Edit>Paste Special - Values. Thanks to Graham for this very quick and easy way.
Ok, onto this months tips. A common question from Excel users is along the lines of; I have a list in a cell that has used Data>Validation with the "List" option. I would like to have another cell display a list that is depended on the item chosen in my first list.Download 2 Free Demos . One is the basic, as explained here, the other is a more advanced version.
There are quite a few ways to achieve this, but the easiest by far is like this.
Open a new Workbook and on Sheet1 enter these heading into cells A1:D1
Expensive Cars
Cities
Names
Countries
Now place some relevant entries below these headings, down to say row 5. Now select A1:D1, click in the Name Box (left of the formula bar) type the name: List1 and push Enter. Now select A1:D5 (or the last row of the longest list) and go to Insert>Name>Create. Ensure only "Top Row" is checked and click Ok. If you now go back to the Name Box you will see 5 Named ranges. Note how Excel has used the Underscore for "Expensive_Cars". This is because Named ranges cannot have spaces. More on this soon.
Now click onto Sheet2 and select cell A1. Go to Data>Validation, choose the "List" option then type:=List1 in the Source box. Ensure "In-cell dropdown" is checked and click Ok. Now select cell A2 and again go to Data>Validation, choose the "List" option then type: =INDIRECT(SUBSTITUTE($A$1," ","_")) in the Source box. Ensure "In-cell dropdown" is checked and click Ok.
Now select a list from cell A1 and you will get the appropriate list in cell A2.
There are a couple of points that we should understand. Normally, a Validation list cannot refer to a list from another Worksheet. This can be overcome by naming the list and using its Name over its address.
The INDIRECT Function return the reference returned by a text string. This means rather than the Validation List in A2 seeing the content of A1 as a text string, it sees it as a range name.
The SUBSTITUTE Function is used to replace any spaces in the text of A1 with a Underscore. This is vital when we have items in List1 like: Expensive Cars.
Download 2 Free Demos . One is the basic, as explained here, the other is a more advanced version.
*PICK AT RANDOM*
Excel has two useful *volatile functions that will produce random numbers. It has the RAND function, which returns an evenly distributed random number greater than or equal to 0 and less than 1. It also has the RANDBETWEEN function, which returns a random number between the numbers you specify.
*A Volatile function is a function that recalculates when you enter any data into any cell, or take any other action. One of the few exceptions is changing the format of a cell.
For example RAND:
=RAND() will produce a random number between 0 and 1. If you want a higher range you can use: =RAND()*100
If you want only whole numbers you can use:=INT(RAND()*100)
For example RANDBETWEEN
=RANDBETWEEN(1,500)
Will produce a random whole number between 1 and 500.
As you can see, both can produce pretty much any sort of random number. But what if you have a list of names and you want to select one at random? It easier than you may think! Follow these simple steps;
Enter a list of names in column A. Now enter the formula below in the cell you want the random name returned.
=INDEX($A:$A,RANDBETWEEN(1,COUNTA($A:$A)),1)
This will pick a name at random from your list in column A. It will also be dynamic in that when/if you add/remove names from the list they will automatically be included/excluded.
If you have a table of data (more than 1 column) and you wish to select an item at random from the table, you could use:
=INDEX($A:$C,RANDBETWEEN(1,COUNTA($A2:$A65536)),RANDBETWEEN(1,3))
This assumes your table 3 columns wide, hence; $A:$C and RANDBETWEEN(1,3) and we do not want row 1 included as it contains headings, hence; COUNTA($A2:$A65536)
In April we uploaded to our site a free open source Excel Add-in containing lot's of custom functions.DOWNLOAD FROM HERE
NOTE: Some bugs have been fixed so you should download the latest version from the URL above.
This month I will again show how to use a few of these and explain the code that drives them.
**Named_Range_Address**
Function that will return the Address of a Named Range;
=Named_Range_Address(Cities,1)
Where Cities is the Named range. The use of the second Optional argument, means the Worksheet name is also returned. For example;
'My Lists'!$B$2:$B$8
If the second Optional argument was omitted, the result would be; $B$2:$B$8
*CODE*
Function Named_Range_Address(Range_Name As Range, _
Optional SheetName As Boolean) As String
Dim strName As String
Application.Volatile
If SheetName = True Then
strName = "'" & Range_Name.Parent.Name & "'!" & Range_Name.Address
Else
strName = Range_Name.Address
End If
Named_Range_Address = strName
End Function
EXPLANATION
The code first checks to see if the second optional argument is used. If it's not, the "SheetName" argument will retain its default False value. The use of any number (in theSheetName argument) greater than 0 will force "SheetName" to become True.
If True, the use of:Range_Name.Parent.Name returns the Worksheet name to the String variable "strName". The use of "'" & Range_Name.Parent.Name & "'!" means the result is: 'My Lists'! as apposed to simply My Lists. This means the result can be used in a formula. The returned text is then joined with: Range_Name.Address to get a result like: 'My Lists'!$B$2:$B$8
If "SheetName" is False (default), only the address is passed to the String variable "strName".
Finally, the result of the String variable "strName" is passed to the Function, Named_Range_Address = strName.
**Sheet_Exists**
Will return TRUE if a sheet you specify exists, and FALSE if it doesn't.
=Sheet_Exists("Sheet3")
Where you wish to know if the Workbook has a Sheet (including Chart sheets) named "Sheet3".
*CODE*
Function Sheet_Exists(Sheet_Name As String) As Boolean
Dim wsheet
Application.Volatile
On Error Resume Next
Set wsheet = Sheets(Sheet_Name)
Sheet_Exists = wsheet.Name = Sheet_Name
On Error GoTo 0
End Function
EXPLANATION
The only argumentSheet_Name stores the text of the string we enter. This string is then used like:Set wsheet = Sheets(Sheet_Name) to Set a variant Object to and Sheet by the name specified. If a sheet by that name does exist, our variant will be successfully Set. If it does not exists, normally a Run Time Error would occur and our variant will not be successfully Set. It's the use of: On Error Resume Next that prevents the Run Time Error.
Nest we use:Sheet_Exists = wsheet.Name = Sheet_Name. The expression "wsheet.Name = Sheet_Name" will return TRUE if our variant was successfully Set and FALSE if not. Now as the Function "Sheet_Exists" is a Boolean, the TRUE or FALSE is easily passed.
Finally we use: "On Error GoTo 0" to reset Run Time Error trapping.
Last month one of our resident forum gurus, Andy Pope, was kind enough to post to our "Hey that is cool" forum a tip namedProgress meters on steroids . I can tell you these are VERY cool indeed!
Until next month, keep Excelling!
ADVERTISEMENTS
Artificial neural network software for stock markets!
MAIN SOFTWARE CATEGORIES