I put together a reporting app in Excel similar to some you may have seen before. You define criteria for different rows and columns and the intersection of these rows and columns are populated with summary data from an outside database at runtime. You can drill down to the detailed data as you would with a pivot table.

The database is completely out of my control and the connection to it is less than reliable.

As a workaround to getting the detail from the DB I am replicating the relevant tables into worksheets. Size isn't an issue since the tables are never more than a few thousand lines or so. Instead or requerying the db when more detail is needed, I'd like the program to query this backup sheet. I know there are about 100 different ways to pull filtered data from a spreadsheet, but other than this piece, I'm done with the app and I'd rather not recreate the wheel.

Is there a way I can query a spreadsheet with standard SQL? The backup data will be in the open workbook calling the query. Is it necessary to create an ODBC connection to the workbook? If so, are there access issues involved since the user will always be in the workbook being queried?