Data Validation Archives

Pivot Multiple Worksheets

Posted on July 20, 2015 by Doug Glancy

As I’ve mentioned before, these days I use Excel more and more for developing and testing SQL code. As part of that I often compare of sets of output from SQL. And as part of that I sometimes I find it useful to pivot multiple worksheets.

For example, I just finished a project of translating a query from one data warehouse to another. The new database has a completely different schema than the old – new tables, new fields, new behaviors. My goal was to develop a query that returned the same results from the new database as those from the old.

To compare the outputs, I created two tables (listobjects) in a single workbook. The first table had a connection to the old data warehouse and uses the old query as its Command Text. The second table is connected to the new data warehouse, and was where I’d test the SQL I was developing.

Especially at first, there were quite a few differences in the output of these two queries in these two tables. Comparing the outputs in a pivot table let me see these differences clearly, both in summary and in detail.

Here’s a very simple example using my trusty pie data. In this example I have two different tables on two sheets with slightly different pie orders. Here’s the output from data warehouse 1…

and here it is from data warehouse 88b…

I conveniently placed differences in the Quantity column near the top, so you may be able to just pick them out by eye. And you may even have caught the one date field discrepancy. However, after combining the two tables into one, adding a “Source” column and then pivoting, the differences become easy to pick out, especially with a little conditional formatting:

In the pivot above, “2”s in the Grand Total column represent all the records where the two queries returned the same results. The “1”s point to the discrepancies.

This is a flexible and powerful comparison method. Benefits include:

You can quickly add or subtract fields from the pivot to pinpoint the differences.
You can change the orders of the fields.
If you add subtotals you can then double-click on those with disrepancies to drill down to just those results.

For a while I created these combined source tables manually, just pasting the two sets of results together, adding a column “Source” column with “DW_1” and “DW_88b.” This worked fairly well, but after several times it cried out for automation.

The VBA below keys off of selected sheets in a workbook. Just select the ones you want to pivot and then run the code. Here you can see that both sheets are selected, and I’ve added the “Pivot Multiple Sheets” macro to the tab’s right-click menu (with MenuRighter, of course).

The code first collects all the data necessary for the connection and then closes the source workbook. (I did this to avoid memory leaks or whatever it is that makes things go wonky if the workbook is open at the same time I’m creating a connection to it.) It uses that data to create the Source and SQL strings. The SQL is just a series of SELECTS, one for each selected worksheet, connected with UNION ALLs.

The newly created worbook contains a table with the connection to the source workbook and a pivot table pointed at that table. The table’s “Source” field becomes a column in the pivot table, containing the names of the two or more worksheets. The rest of the table columns become pivot table row fields. The connection in this workbook is live, so that if you make changes to the source they will appear in this workbook once you refresh the data and pivot:

Sub Pivot_Multiple_Sheets()

Dim wbToPivot As Excel.Workbook
Dim SheetsToPivot As Excel.Sheets
Dim SourceFullName As String
Dim SourceString As String
Dim wbWithPivot As Excel.Workbook
Dim wsWithQueryTable As Excel.Worksheet
Dim SheetsToPivotCount As Long
Dim SheetsToPivotNames() As String
Dim qt As Excel.QueryTable
Dim i As Long
Dim SqlSelects() As String
Dim sql As String
Dim pvt As Excel.PivotTable
Dim pvtField As Excel.PivotField

If ActiveWorkbook Is Nothing Then
MsgBox "No active workbook."
Exit Sub
End If
Set wbToPivot = ActiveWorkbook

If Not wbToPivot.Saved Then
MsgBox "Please save this workbook before running." & vbCrLf & _
"Workbook will be closed by this utility" & _
"after the process is completed."
Exit Sub
End If

'This code acts on the Selected Sheets
Set SheetsToPivot = wbToPivot.Windows(1).SelectedSheets
If SheetsToPivot.Count = 1 Then
MsgBox "Please select two or more worksheets (no charts)."
Exit Sub
End If

SheetsToPivotCount = SheetsToPivot.Count

For i = 1 To SheetsToPivotCount
If Not TypeName(SheetsToPivot(i)) = "Worksheet" Then
MsgBox "Please select two or more worksheets (no charts)."
Exit Sub
End If
Next i

SourceFullName = wbToPivot.FullName
ReDim SheetsToPivotNames(1 To SheetsToPivotCount)
For i = 1 To SheetsToPivotCount
SheetsToPivotNames(i) = SheetsToPivot(i).Name
Next i
'Change Selection to only one sheeet
SheetsToPivot(1).Select
'Close the source workbook before creating the new one and its connections
'Save it so not prompted
wbToPivot.Close True

Set wbWithPivot = Workbooks.Add
'Delete any extra worksheets
For i = wbWithPivot.Worksheets.Count To 2 Step -1
Application.DisplayAlerts = False
wbWithPivot.Worksheets(i).Delete
Application.DisplayAlerts = True
Next i
Set wsWithQueryTable = wbWithPivot.Worksheets(1)
wsWithQueryTable.Name = "Data Table"
'Don't know why this is needed, but otherwise .CommandText line below fails
wsWithQueryTable.Activate

'I got rid of a lot of fields in connection - still seems to work
SourceString = "ODBC;DSN=Excel Files;DBQ=" & SourceFullName
'Create an array of SELECT statements
ReDim SqlSelects(1 To SheetsToPivotCount)
For i = 1 To SheetsToPivotCount
SqlSelects(i) = "SELECT" & vbCrLf & _
"'" & SheetsToPivotNames(i) & "' as Source," & vbCrLf & _
"Sheet" & i & ".*" & vbCrLf & _
"FROM" & vbCrLf & _
"`" & SourceFullName & "`.[" & SheetsToPivotNames(i) & "$] AS Sheet" & i
Next i
'Connect the SELECTS with UNION ALL
For i = LBound(SqlSelects) To UBound(SqlSelects) - 1
sql = sql & SqlSelects(i) & vbCrLf & "UNION ALL" & vbCrLf
Next i
sql = sql & SqlSelects(i)

Set qt = wsWithQueryTable.ListObjects.Add(SourceType:=0, Source:=SourceString, Destination:=wsWithQueryTable.Range("$A$1")).QueryTable
With qt
.CommandText = sql
.ListObject.DisplayName = "tbl" & Format(Now(), "yyyymmddhhmmss") & Right(Format(Timer, "#0.00"), 2)
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
'I like it to preserve the widths the first time it's run, and below turn it to false
.AdjustColumnWidth = True
.Refresh BackgroundQuery:=False
.AdjustColumnWidth = False
End With
wbWithPivot.Worksheets.Add
With ActiveSheet
.Name = "Pivot"
Set pvt = .Parent.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=qt.ListObject.Name).CreatePivotTable(TableDestination:=.Range("A1"))
pvt.AddDataField Field:=pvt.PivotFields("Source"), Function:=xlCount
With pvt.PivotFields("Source")
.Orientation = xlColumnField
.Position = 1
End With
For Each pvtField In pvt.PivotFields
If pvtField.Name <> "Source" Then
pvtField.Orientation = xlRowField
pvtField.Position = pvt.RowFields.Count
End If
Next pvtField
End With
End Sub

To use this code put it in your Personal.xlsb or any workbook besides the one with the source data.

This code could use some more error-checking. For example, if the two sheets have a different number of columns. Even more important is the addition of whatever kind of general error handling you use so you exit gracefully from bad connection strings and other such inevitable problems.

Speaking of bad connection strings, you may notice that I’ve ditched the Default Directory, DriverId, BufferSize, MaxPageTimeOuts and whatnot from the connection. I did that to see if it worked. It did, so I never added them back. I see that they reappear in the connection properties for the table:

I ran this code in Excel 2010 and 2013. I don’t know how portable this code is to other Excel versions. I also don’t know if you’ll have performance issues if you have the source and pivot workbooks open at the same time.

If you’re interested in this topic be sure to take a look at Kirill Lapin’s method, posted on Contextures. His method keeps the source and the pivot table in one workbook, deleting the connection in between refreshes of the pivot table. I think Kirill’s method is nice for more traditional pivot table use where you want to merge different data sets with the same format, e.g., eastern and western sales regions.

I like my method because it requires no setup for the source workbook, keeps a refreshable connection and arranges the pivot table for comparison.

I’d love to hear anybody’s opinion on the stability of this method, i.e., when opening both the source and the connected data at the same time. Also, I’m curious if this code works in other versions besides 2010 and 2013. These are areas where my knowledge is pretty piecemeal, so any help would be appreciated.

Importing SQL Files Into Data Connections

Posted on December 13, 2014 by Doug Glancy

By now you may know that I love data connections in Excel. Sometimes I use them for the front-ends in finished projects, but mostly I use them for testing SQL. With its formulas, tables and pivot tables, Excel makes a great test environment for validating SQL results. You can of course just paste query output straight from SQL Server Management Studio or other development environments, but the it doesn’t always format correctly. For instance Varchar ID fields that are all numbers lose leading zeros and dates lose their formats. In my experience those problems don’t happen with data connections

In this post, we’ll start with the basics of a reusable Table/SQL connection to which you can then add your SQL. Then I’ll share some code that lets you point at one or more .sql files and creates a connected table for each one. (An .sql file is just a text file with SQL in it and an .sql extension for handy identification.)

A Reusable Table/SQL Connection

At work I have a default data connection to the main database we query, all set up with the Connection, Command Type and some dummy Command Text. Whenever I want to run some SQL against that database in Excel, I just click on that connection in Data > Existing Connections. If I worked at home and used SQL Server and kept the corporate database on my laptop, the connection could look like this.

I created it by going to Data > Connections > Other Sources > From SQL Server. After following the wizard, I modified the connection by changing the Command Type to SQL and the Command Text to the meaningless, but super-speedy query “SELECT ‘TEMP’ FROM TEMP.”

So now I’ve got a template I can call from Data > Existing Connections and quickly modify the SQL, say to something like:

Inserting SQL Directly From .sql Files

Recently I thought I’d take this a bit further and pull the CommandText directly from an .sql file. So I wrote some code that has you pick one or more .sql files, and then creates a new Worksheet/Table/Query for each one in a new workbook. The main query is below. The heart of it looks a lot like what you got if you ran the macro recorder while creating a new connection:

Sub AddConnectedTables()
Dim wbActive As Excel.Workbook
Dim WorksheetsToDelete As Collection
Dim ws As Excel.Worksheet
Dim qt As Excel.QueryTable
Dim sqlFiles() As String
Dim ConnectionIndex As Long

sqlFiles = PickSqlFiles
If IsArrayEmpty(sqlFiles) Then
Exit Sub
End If

Workbooks.Add
Set wbActive = ActiveWorkbook
'Identify the empty sheet(s) the workbook has on creation, for later deletion
Set WorksheetsToDelete = New Collection
For Each ws In wbActive.Worksheets
WorksheetsToDelete.Add ws
Next ws

For ConnectionIndex = LBound(sqlFiles) To UBound(sqlFiles)
wbActive.Worksheets.Add after:=ActiveSheet
'*** Modify the location below to match your computer ***
Set qt = ActiveSheet.ListObjects.Add(SourceType:=0, _
Source:="ODBC;DSN=Excel Files;DBQ=E:\DOCS\YOURSUMBUDDY\BLOG\POST_72_SQL_IMPORTER\Post72_Data.xlsx;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;", _
Destination:=Range("$A$1")).QueryTable
With qt
'Temporary command text makes the formatting for the real query work
.CommandText = ("SELECT 'TEMP' AS TEMP")
.ListObject.DisplayName = "tbl" & Format(Now(), "yyyyMMddhhmmss") & "_" & ConnectionIndex
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
'Refresh first with just the template query
.Refresh BackgroundQuery:=False
.CommandText = ReadSqlFile(sqlFiles(ConnectionIndex))
'Refresh again with the new SQL. Doing this in two steps makes the formatting work.
.Refresh BackgroundQuery:=False
.AdjustColumnWidth = False
'Name the just-created connection and table
.ListObject.DisplayName = Replace("tbl" & Mid$(sqlFiles(ConnectionIndex), InStrRev(sqlFiles(ConnectionIndex), Application.PathSeparator) + 1, 99) & Format(Now(), "yyyyMMddhhmmss") & "_" & ConnectionIndex, ".sql", "")
wbActive.Connections(1).Name = .ListObject.DisplayName
End With
Next ConnectionIndex

'Delete the empty sheet(s) the worbook had on creation
Application.DisplayAlerts = False
For Each ws In WorksheetsToDelete
WorksheetsToDelete(1).Delete
Next ws
Application.DisplayAlerts = True
End Sub

Notice that the code refreshes the querytable twice. If I just go straight to the query from the .sql file, I end up with the same type of formatting problem described at the beginning of this post. For example, dates come through without formatting, like 41985. Starting with a dummy query of SELECT ‘TEMP’ AS TEMP, refreshing it, setting the .CommandText to the correct SQL and refreshing again results in correct formatting.

The code also sets .AdjustColumnWidth twice because I like to start with correct column widths and then not have them adjust after that.

You’ll also note that the connection in the code above isn’t to a SQL Server database anymore, but to an Excel workbook. That’s because I created a downloadable folder for you to try this out in, and the easiest data source to include is an Excel workbook. See the end of this post for the link and a few instructions.

(Also as a weird bonus in the code above is something I came up with to delete the one or more vestigial empty worksheets that get created in a situation like this where your creating a new workbook in code.)

Below are the three functions called from the module above. One uses a File Dialog to pick one or more .sql files.

Private Function PickSqlFiles() As String()
Dim fdFileDialog As FileDialog
Dim SelectedItemsCount As Long
Dim sqlFiles() As String
Dim i As Long

Set fdFileDialog = Application.FileDialog(msoFileDialogOpen)
With fdFileDialog
.ButtonName = "Select"
.Filters.Clear
.Filters.Add "SQL Files (*.sql)", "*.sql"
.FilterIndex = 1
.InitialView = msoFileDialogViewDetails
.Title = "Select SQL Files"
.ButtonName = "Select"
.AllowMultiSelect = True
.Show
If .SelectedItems.Count = 0 Then
GoTo Exit_Point
End If
SelectedItemsCount = .SelectedItems.Count
ReDim sqlFiles(1 To SelectedItemsCount)
For i = 1 To SelectedItemsCount
sqlFiles(i) = .SelectedItems(i)
Next i
End With
PickSqlFiles = sqlFiles

Exit_Point:
End Function

This one returns the SQL from the .sql file, so that it can then be stuffed into the QueryTable’s .CommandText property:

Private Function ReadSqlFile(SqlFileFullName As String)
Dim SqlFileLine As String
Dim Sql As String

Open SqlFileFullName For Input As #1
Do Until EOF(1)
Line Input #1, SqlFileLine
Sql = Sql & SqlFileLine & vbNewLine
Loop
'Sql = Input$ '(LOF(#1), #1)
Close #1
ReadSqlFile = Sql
End Function

And this is Chip Pearson’s code for checking if an array, specifically that returned by the PickSqlFiles function, is empty:

Public Function IsArrayEmpty(Arr As Variant) As Boolean
'Chip Pearson
Dim LB As Long
Dim UB As Long

Err.Clear
On Error Resume Next
If IsArray(Arr) = False Then
' we weren't passed an array, return True
IsArrayEmpty = True
End If
UB = UBound(Arr, 1)
If (Err.Number <> 0) Then
IsArrayEmpty = True
Else
Err.Clear
LB = LBound(Arr)
If LB > UB Then
IsArrayEmpty = True
Else
IsArrayEmpty = False
End If
End If
End Function

Download and Instructions

This download marks a new level of complexity, so it’s got instructions.

After you download you’ll need to unzip the folder to wherever you want. It contains five files, the xlsm with the code, the workbook data source and three .sql files with queries against that data source:

There’s further instructions in the xlam file. As noted there, you’ll need to change the path in the VBA to your unzipped folder (technically, you don’t because Excel will prompt you when it can’t find the folder in the VBA, but it will be cooler if you do). There’s a handy Cell formula in the Post72_Import_SQL.xlsm which will give you the correct file path.

Here’s the downloadable folder. Let me know what you think!

Prompt to Add New Items to ComboBox or Data Validation

Posted on June 2, 2013 by Doug Glancy

Microsoft Access ComboBoxes have a handy NotinList event which allow you to check whether a value entered in a combobox is already in its list. If it’s not you can ask the user whether to add it. This post shows how to mimic that functionality in a combobox on a VBA userform. I also show how to do the same thing with a data validation list.

Creating a ComboBox NotInList Event

The key to doing this is checking the value of the ComboBox’s “MatchFound” property in its Exit event. If no match is found, we ask the user whether to add the item to the list of valid items (hats in this case). If the answer is “Yes” then a row with the hat is added to the table. If not, we clear the combobox and keep the focus on it. You can see this in action in the video above.

Here’s the code for the combobox’s Exit event:

Private Sub cboHats_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim loValidationSource As Excel.ListObject
Dim loRow As Excel.ListRow

'the Table with the list of valid hats
Set loValidationSource = wsTables.ListObjects("tblValidationSource")
With Me.cboHats
'We're only interested if these aren't true
If .MatchFound Or .Value = "" Or .Value = STARTING_VALUE Then
Exit Sub
End If
'If the hat entered isn't in list, prompt to add it
If MsgBox(.Value & " is not in the list. Add it?", vbYesNo + vbDefaultButton2 + vbQuestion) = vbYes Then
Set loRow = loValidationSource.ListRows.Add
loRow.Range.Cells(1).Value = .Value
SortSourceTable
RefreshComboList
Else
'if "no", keep focus on the ComboBox and set it's value to "Choose a hat"
Cancel = True
Me.cboHats.Value = STARTING_VALUE
End If
End With
End Sub

One important thing is that the combobox’s “MatchRequired” property must be set to False (which is the default). Otherwise the Exit will be preempted by an “Invalid Property Value” message from Excel.

Creating a Data Validation NotInList Event

As with the combobox version, we use an event to prompt the user whether to add an item that’s not in the list. This time we use our own “MatchFound” function to check against the data validation’s source list. Similar to setting the “Match Required” combobox property to False, the data validation version requires that the “Show error alert after invalid data is entered” is unchecked in the data validation setup dialog. This is obviously not the default:

Since I’m working in Excel 2010, I’ve created a single-column table (listobject) to hold the valid items. I then simply pointed the data validation’s Source property at the column, excluding the header. Because the source is in a table, it’s dynamic – it adjusts when you add or remove items from the column. No dynamic ranges are required, just select the cells:

Here’s the code from the ThisWorkbook module, which contains the Workbook_SheetChange event and the MatchFound function:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim cell As Excel.Range
Dim loValidationSource As Excel.ListObject
Dim loHatCollection As Excel.ListObject
Dim loRow As Excel.ListRow

'wsTables is the sheet's CodeName
If Not Sh Is wsTables Then
Exit Sub
End If
Set loValidationSource = wsTables.ListObjects("tblValidationSource")
Set loHatCollection = wsTables.ListObjects("tblHatCollection")
'only continue if change is in column with data validation
If Intersect(Target, loHatCollection.ListColumns("Hat Type").DataBodyRange) Is Nothing Then
Exit Sub
End If
With Intersect(Target, loHatCollection.ListColumns("Hat Type").DataBodyRange)
For Each cell In .Cells
If MatchFound(cell.Value) = False And cell.Value <> "" Then
If MsgBox(cell.Value & " is not in the list. Add it?", vbYesNo + vbDefaultButton2 + vbQuestion) = vbYes Then
Set loRow = loValidationSource.ListRows.Add
loRow.Range.Cells(1).Value = cell.Value2
Else
cell.ClearContents
End If
End If
Next cell
End With
SortSourceTable
End Sub

Function MatchFound(ValueToCheck As Variant) As Boolean
Dim loValidationSource As Excel.ListObject
Dim ValidationList As Excel.Range

Set loValidationSource = wsTables.ListObjects("tblValidationSource")
Set ValidationList = loValidationSource.ListColumns("Hats Validation List").DataBodyRange
MatchFound = Application.WorksheetFunction.CountIf(ValidationList, ValueToCheck) > 0
End Function

And here’s what it looks like in action:

The Sort object – Excel 2007 Onwards

My code uses VBA’s Sort object, which appeared in Excel 2007. I like the way it works. You add Sort Fields, just as you do in the user interface, and then apply the sort when needed. If you are using Excel 2003 or earlier you’d need to re-write the two sorting procedures to work with your version.

Also, if you are using Excel 2003 or earlier, see this Contextures post for a non-table way of automatically adding items to a data validation list. You could easily add the code to prompt the user whether to do so.

Download

Here’s a workbook with all the code for both versions.

User-Friendly Survey Without VBA

Posted on August 7, 2012 by Doug Glancy

A few times a year I email workbooks containing surveys to people at about 80 schools. The overall process goes something like: I get a list of a couple of thousand records, which is then split into multiple per-school survey workbooks, which are then emailed to the schools. School staff complete the surveys and email them back. They are then merged back into one file and analyzed. Until we figure out some kind of web-based SharePoint-type system, this all works pretty well. I’ve mostly automated the emailing – adjusting subject lines, recipient lists, attachments and body on a per-school basis – and the splitting and re-merging is all push-button, so it’s a pretty efficient process.

The workbook/surveys are designed to be as user-friendly as possible, both for the people completing them and for the those analyzing the results. I use a combination of data validation and conditional formatting to guide the recipients. Ideally this might also include some VBA for things the data validation can’t handle, but it’s not worth confusion and maintenance issues that would result. So instead the workbooks contain additional conditional formatting that warns people when their data entry has gone astray. It also uses a concept I think of as conditional named ranges to provide appropriate data validation choices.

In the above picture, I’ve imagined some kind of International Pie Lovers Association, with a survey for their annual dinner. The meal choices are simple (Yes/No and Veggie/Meat) but they can choose up to three slices of pie, with a separate data validation dropdown for each slice. Yellow cells indicate where choices need to be entered. Orange indicates an error: cells that were entered when a condition called for it, but where that condition is no longer true, for example, under pie slices “3” was entered originally and “Banana Cream” was chosen, but then the number of slices was reduced to “2.”

In order to make data entry more user-friendly, I came up with data validation which points at a named range that resolves to one or more cells if a condition is true, and to nothing if it’s not. In the picture below, the condition is true in the first cell in the “Pie 1” column and so a list of pies is available, but if you clicked the dropdown in the “Pie 2” column there would be no choices. That’s because the user chose 1 in the “Pie Slices” column, so the conditional range equates to nothing for the “Pie 2” column.

There are many ways to do condtional data validation and Debra Dalgleish has lots of great info on her website. In the dark days before I had my own blog she was kind enough to post about this particular conditional range concept.

To set up the conditional range, I first create a named range called “rngPies” that points at a static list of pies in column M. Then I create the conditional range, called “rngValPie,” which points at rngPies if the condition is met, and points at nothing if it’s not. The formula for rngValPie (with I2 selected) is:

=IF(Sheet1!$H2+1>=COLUMNS(Sheet1!$H2:I2),rngPies,)

In English it says “If the number of slices selected is less than or equal to the number in this column, use rngPies, otherwise use nothing.” Here it is in the indispensable Name Manager.

The data validation then points at rngValPie. If a pie should be chosen the data validation shows the list, otherwise there’s no choices available.

Note that when you enter a conditional range in the Data Validation dialog, the condition needs to be true, otherwise you’ll get an error message. For instance, if I try to enter the data validation while J2 is the active cell, I’ll see this:

Turning the cells orange if unneeded data is entered is accomplished with conditional formatting and some helper columns to the left of the data entry area.

The helper columns contain formulas that feed the conditional formatting. I could put the formulas from the helper columns directly in the conditional formatting, but do it this way because it’s easier for us back at the office to spot invalid data by filtering the helper columns.

This system works well for us and for the folks at the schools (at least as far as I can tell). The amount of user-helpfulness is in good balance with the ease of maintenance. If you’d like to look at a sample workbook that’s Excel 2003-2010 compatible, here’s the zipped file.

Note: for another use of conditional ranges, which has worked very well for me, see this Jan Karel Pieterse post on Daily Dose of Excel.