Linking Excel Named Ranges in Visio

I have been using worksheets and ranges in Excel with Visio’s Link Data to Shapes (in the Pro and Premium editions) for several years, but recently encountered a problem with the recently added structured references in Excel. Fortunately, with the help of my colleague John Goldsmith, we found a solution.

Normally, I create tables in Excel worksheets with the column headings in the first row, so I could use the worksheet name in the Link Data to Shapes function in Visio. However, I wanted to have an extra row above the table headers for a recent project, so I wanted to use named ranges, defined with any rows above the table headers omitted. When I went to connect these ranges using the Data Selector in Visio, I found that all of my named ranges, except one (StepIds), were missing from the list of worksheets and ranges:

So, we explored the ranges using the Name Manager in Excel, and noticed that all of the missing ranges were using the structured reference notation:

The solution is un-tick the Use table names in formulas option on the Formulas tab in the Working with formulas group:

This meant that I was able to define and name ranges without the structured references:

Now, these newly created named ranges do become selectable with the Data Selector in Visio, and I can thus create my linked External Data recordsets:

You can read about structured references at http://office.microsoft.com/en-us/excel-help/using-structured-references-with-excel-tables-HA010342999.aspx