In a social.answers forum, Sue asked if there was a way to automatically copy rows that had a blank value in a particular column. See http://social.answers.microsoft.com/Forums/en-US/excslcreate/thread/dd6af3c8-5801-43c0-86f4-a668cdc3fd85

As a fan of using SQL, particularly through MS Query, I came up with the following.

Suppose we have a table with 3 columns, with a few empty values in the column labeled b.

Next, save this workbook. I saved it as Book1.xlsx.

In a new workbook, select Data tab | Get External Data group | From Other Sources dropdown | from Microsoft Query button.

In the Choose Data Sources dialog box, in the Databases tab, select Excel Files* and uncheck ‘Use the Query Wizard to create/edit queries’. Click OK to get to MS Query with a dialog box to select a workbook. Select the workbook saved above.

In the Add Tables dialog box, select the sheet in which you have the source data table and click Add. Close the dialog box.

Double click each field to add it to the query result.

Select the menu item Criteria | Add Criteria… to get the Add Criteria dialog box. In it select the column named b and from the Operator dropdown select ‘Is Null’ Click Add then close the dialog box.

In MS Query, select File | Return Data to Microsoft Office Excel

Back in Excel, in the Import Data dialog box, specify where to put the result.

The result will be

To refresh the query, select any cell in the result table and click the Data tab | Connections group | Refresh All button.

You can also specify that the result be automatically refreshed every N minutes or upon workbook open. Use Data | Connections group | Refresh All dropdown | Connection Properties… button.

For those curious about the resulting SQL, it is in the Connection Properties | Definition tab.