Hi all
Firstly I should apologise as I'm a bit late with this months newsletter. It's been a very busy month for me as I've been inundated with requests for VBA for Excel training. I have been getting some feedback from some of my students on why they chose remote training/tutoring over other types. The five most popular reason to date have been:
- They can do the lessons when it suites them.
- They can keep the lesson as a hard copy and reference it anytime and any place.
- They can ask any (and as many) questions they need to on the lessons.
- The course content can be made harder or easier at their request.
- The entire course can be rotated around a real project they are working on.
Why Use Us for Excel and VBA for Excel Remote Training?
OzGrid specializes in Microsoft Excel, this is virtually all we do! Over time we have focused our business directly towards Excel. Other training companies will offer training in many different Applications, and while they may supply information from good trainers it is unlikely the trainers will know Excel to the extent we do! Remember we develop Excel spreadsheets in our day-to-day business so we know what you will encounter in your day-to-day use of Excel.
Our lessons contain virtually no *graphics, we lean very heavily toward content only. I have seen many lessons that have loads of space filling graphics that go through the ABC's, but there is little content. We know that using Excel is rarely as straight forward as ABC in the real world. We show you how to help yourself and solve real Excel problems.
We encourage questions and feedback. The courses are not fixed in their structure, you can lean your course towards the aspect of Excel you want to learn.
* I firmly believe that using graphics in training (especially remote training) encourages the student to not think! By focusing on content only, both the teacher (us) and the student are forced onto the same wavelength.
The other news from OzGrid this month is a new add-in that I will be having beta tested by some heavy Excel users within the next 1 to 2 weeks. If all goes well (only a few bugs) it should be ready to sell within 4 to 6 weeks. the features of the add-in will be:
- Formula report generator
- Dynamic range creator
- Real calendar creator
- Sheet namer, creator and sorter.
- Random number generator
- Conditional row deletion
- Duplication reporter
- Do on time.
For this months first tips I thought we would get back to the basics of Worksheet formulas. Below are two formulas that I have written by request for a couple of people. I have not put them to the proper test yet myself, so if you encounter any problems please let me know.
Convert a date to read th,rd,st or nd
=IF(OR(DAY(NOW())=3,DAY(NOW())=23),TEXT(NOW(),"dddd d") & "nd" & TEXT(NOW(), "mmm yyyy"),IF(OR(DAY(NOW())=1,DAY(NOW())=21,DAY(NOW())=31),TEXT(NOW(),"dddd d")& "st" & TEXT(NOW(), "mmm yyyy"),IF(OR(DAY(NOW())=2,DAY(NOW())=22),TEXT(NOW(),"dddd d") & "nd" & TEXT(NOW(), "mmm yyyy"),TEXT(NOW(),"dddd d") & "th " & TEXT(NOW(),"mmm yyyy"))))
Proper surnames that have Mc or Mac
=IF(LEFT(A5,3)="mac","Mac"&PROPER(RIGHT(A5,LEN(A5)-3)),IF(LEFT(A5,2)="mc","Mc"&PROPER(RIGHT(A5,LEN(A5)-2)),PROPER(A5)))
The final one is a Custom function (or User Defined Function) that will allow the user to nominate 2 lookup values in 2 seperate columns as well as the occurence number of the value to find. It works in a very similar way to the standard VLOOKUP, except as I've said you can nominated 2 values and the occurrence number.
Function FindNth(Table As Range, Val1 As Variant, _
Val1Occrnce As Integer, Val2 As Variant, _
Val2Col As Integer, ResultCol As Integer)
'Written by OzGrid Business Applications
'www.ozgrid.com
'Finds the N'th value in the first Column of _
a table that has a stated value on the same _
row in another Column.
Dim i As Integer
Dim iCount As Integer
Dim rCol As Range
For i = 1 To Table.Rows.Count
If Table.Cells(i, 1) = Val1 And _
Table.Cells(i, Val2Col) = Val2 Then
iCount = iCount + 1
End If
If iCount = Val1Occrnce Then
FindNth = Table.Cells(i, ResultCol)
Exit For
End If
Next i
End Function
..To use it, push Alt+F11 then go to Insert>Module and paste it in. Push Alt+Q to return to Excel and save.
Now put this in any cell:=FindNth(A1:C100,"Dog",2",Male",3,5)
Where: A1:C100 is the entire table.
"Dog" is the value to find in the first column of A1:C100
"2" is the occurence of "Dog" to find in the first Column of A1:A10
"Male" is the second value to find.
"3" is the relative position of the column in A1:C100 to find "Male"
"5" is the relative position in the column to return our result from
In other words, find the second occurence of a Male Dog and return the result from the same row in the fifth column. You can see this better if you push Shift+F3 and scroll down to "User defined" and then select "FindNth".
Till next time, keep well!