This tutorial will show you how to create a dependent list on Excel. This technique is extremely useful when categorizing data. In the example below, the user will select the State in cell A3. Cell B3 (City) will pull a city based off of the users selection

Last month Microsoft released the beta version of Office 2010. The new version includes new versions of all the classics: Word, Powerpoint, Sharepoint and our favorite, Excel 2010! Here is a short video from Microsoft about the new changes: See What’s New in Microsoft Excel 2010

Excel has a strong and often missed feature, called Conditional Formatting, which allows you to format a cell depending on a rule [it's value]. This can be very useful for worksheets like Product Quantities and Dates. Let me show you what I mean. Lets start off with a simple chart of products quantities in the [...]

The following routine will sort the sheets in a workbook alphabetically. The flag “Sort_Mode_Descending” can be switched between descending and ascending as required. The routine is case INSENSITIVE. Sub Sort_Sheets() Dim Sort_Mode_Descending As Boolean Dim No_of_Sheets As Integer Dim Outer_Loop As Integer Dim Inner_Loop As Integer No_of_Sheets = Sheets.Count ‘Change Flag As appropriate Sort_Mode_Descending = [...]

The standard Excel “Column” Function returns the number rather than the letter of the column e.g: Column(E4) – returns the number 5 rather than the letter E Column(AD12) returns the number 30 rather than AD. The following function returns the letter rather than the number of the column. So in the above two examples we [...]

The following function will extract the numerical portion from a string: Function Extract_Number_from_Text(Phrase As String) As Double Dim Length_of_String As Integer Dim Current_Pos As Integer Dim Temp As String Length_of_String = Len(Phrase) Temp = “” For Current_Pos = 1 To Length_of_String If (Mid(Phrase, Current_Pos, 1) = “-”) Then Temp = Temp & Mid(Phrase, Current_Pos, 1) [...]

The following function will find the nth word in a string: Function Find_nth_word(Phrase As String, n As Integer) As String Dim Current_Pos As Long Dim Length_of_String As Integer Dim Current_Word_No As Integer Find_nth_word = “” Current_Word_No = 1 ‘Remove Leading Spaces Phrase = Trim(Phrase) Length_of_String = Len(Phrase) For Current_Pos = 1 To Length_of_String If (Current_Word_No [...]

The following code works opening a workbook. It automatically adds a new sheet and labels it with the date. It also checks to see that the sheet doesn’t already exist – to allow for the possibility of it being opened more than once a day. This code makes use of the Workbook Open Event and [...]

The following code will prompt the user for the name of a workbook. It will then create and save a new workbook with the same name. Private Sub CommandButton1_Click() Dim Workbook_Name As String Dim New_Workbook As Workbook Set New_Workbook = Nothing Workbook_Name = InputBox(Prompt:=”Workbook Name.”, Title:=”Enter the WorkBook Name :”) Set New_Workbook = Workbooks.Add With [...]

The following UDF will return the formulae for a given cell reference: Function Show_Cell_Formulae(Cell As Range) As String Show_Cell_Formulae = “Cell ” & Cell.Address & ” has the formulae : ‘ ” & Cell.Formula & ” ‘” End Function So if we have: Where the cell B7 has the formulae : ‘=MAX(B5:G5) then we can [...]