## Calculate Sales Commission With Vlookup Sale Amount Tiers

1 minute read Learn how to calculate commissions for a basic tiered plan and rate table using the VLOOKUP function. This tables shows a simple commission plan with a rate table that lists the payout rate at each level of sales. Using the Vlooup function, set the match type to TRUE (you can even leave … Read more

## Function To Test An Entire Column Or Row Has Been Selected

1 minute read Function to test an entire column or row has been selected. Using ths function before commencing your macro is a great idea if the macro is using the selection as a variable range. ‘============================================================================================ ‘ ## Checks whether an entire column or row has been selected and notifies the user ‘ ‘ … Read more

## Hide Zero Values In Data Labels

less than 1 minute read So you have a 0% value on one of your data labels and want to hide it? The quick and easy way to accomplish this is to custom format your data label. Select a data label. Right click and select Format Data Labels Choose the Number category in the Format … Read more

## Calculate Time Between Dates

1 minute read Download the example workbook here: calculate-time-between-dates.xlsx Calculating hours, minutes and seconds between two times =IF(B2< A2, 1 + B2 – A2, B2- A2) Number Format: h:mm:ss Calculate days To calculate elapsed days is so easy, you just need to apply this formula Number Format: Calculate Days, Hours and Minutes To calculate and … Read more

## Highlight The Row And Column Of The Selected Cell

1 minute read Here’s the simple steps to highlight the row and column of the selected cell which can be extremely useful when navigating large sets of data. Here’s a little example: Download the example workbook here: Highlight the Row and Column of the Selected Cell.xlsx Select the data set in which you to highlight … Read more

## Generate A Guid To A Selected Range Of Cells With Vba

2 minute read A GUID (or UUID) is an acronym for ‘Globally Unique Identifier’ (or ‘Universally Unique Identifier’). It is a 128-bit integer number used to identify resources. The term GUID is generally used by developers working with Microsoft technologies, while UUID is used everywhere else. How unique is a GUID? 128-bits is big enough … Read more

## Function To Test If The Workbook Is Protected

less than 1 minute read Function to test if the workbook is protected. ============================================================================================= ‘ ## Tests if the activeworkbook is protected ‘ ‘ Test if the workbook is protected ‘ If WorkBookProtected = True Then Exit Sub ‘============================================================================================ Function WorkBookProtected() As Boolean With ActiveWorkbook If .ProtectWindows Or .ProtectStructure Then WorkBookProtected = True MsgBox “This … Read more

## Converting Column Numbers To Letters

1 minute read Obtaining Row information is easy since Rows are always Numbers. Column Letters that can be used in a Range are a little more tricky. I have many methods to obtain a Column Letter from a Column Number here are a few of my favourites: ‘================================================================================================== ‘ ## Function to get the column … Read more

## Copy A Formula Down A Dynamic Column Range

1 minute read Copy Formula down a dynamic Column Range with or without AutoFill or using FillDown You can use the following methods to Copy Formula down a dynamic Column Range (assumes data is in Column A with a Header & Formulas are in Columns “B:D”): Dim lngLastRow As Long lngLastRow = Range(“A” & Rows.Count).End(xlUp).Row … Read more

## Function To Get The Unique Count In A Range

1 minute read User Defined Function to get the unique count in a range. ‘ This is an Ultra-fast UDF to use in Excel or VBA to derive a Unique Count from a Range. ‘ The Code works by building a Dictionary of the Unique values. You can pass the Range as ‘ a Worksheet … Read more