Excel 2007 : SQL Connection How To Questions – Excel 2007

[ckoster]

Greetings Excel Experts (Excelperts)!

I have what is most likely a fundamental how-to Excel question that I’ve been unable to answer myself – even after a few hours of trial and error poking around.

I have a SQL data source that contains sales data. I would like to build a matrix within excel where each cell in the matrix pulls unique data from the SQL Server and presents the resultant value. In all cases I will be counting the number of records that meet a specific condition using SQL.

I was able to accomplish this for a single cell in my matrix by configuring clicking on a cell and creating a connection to the data source (Data -> Connections…etc). I then specify the query and click ok. I get the result I want in the cell I want – perfect.

So, using this strategy I would have to create a connection for each of the 200+ cells in my matrix. My question is this….is there a simplified way to share the connection to the data source and then specify a unique query for each cell in order to get the results I want?

Can anyone point me in the right direction here?

Thank you in advance.

-Carl

[Paul]

Hi Carl,

I don't work with SQL in Excel much, but to me that seems like an awful lot of work that could much more easily be accomplished by either:

1. Using a professional reporting tool like Crystal; or
2. Using one query to grab all the data you need into Excel then summarizing it the way you need on another sheet or in another workbook; or
3. Using Access