Creating an Excel Spreadsheet using SQL view that filters on a cell reference
The first step is to create your view in SQL Management Studio. The view should contain all the information you will want to display.
Next Open Microsoft Excel
Setup your Data Source. Click the Data tab -> From Other Sources -> From Microsoft Query
Select <New Data Source> and click OK.
Give your data source a recognisable name and setup your connection to SQL Server. Click Connect and fill out the details that you use to log into SQL Server. Click OK.
Choose your newly created Data Source and untick ‘Use the Query Wizard to create/edit queries’. Then press OK.
Next, select your view you want to use from the list and click Add. Then Click Close.
Click the * in the table of your view to select all fields, or if you only want to display certain fields you can individually pick them.
Next, we need to add the criteria you want to filter on using the cell reference. Click View -> Click Criteria.
Inside the ‘Criteria Field’ select the field you want to filter on, in our case we are using Account Number. Next enter something within square brackets in the value section.
Click Cancel on the pop-up box.
Next, click File and then click Return Data to Microsoft Excel.
Choose where you want your table to be placed. In my case, I want it in a new worksheet. Then click OK.
Choose the cell you want to reference. In my case, I am using A2 on the first sheet. Tick the two tick boxes then click OK.
That’s it! You are now able to change the value of your cell which will be reflected in the table connecting to your view in SQL!