Targeting the query results to the dashboard

Let's set a result destination for our query results on the dashboard next to our input values.

Let's say we want our result values to be placed starting at cell D2 on the Dashboard worksheet:

Click the _1010q Sheet tab to modify the q-sheet. The Result Destination is a reference to the top left cell of the range where the query results will be pasted. This can be an address or a defined name. Let's create a defined name for cell D2 on the Dashboard worksheet.

On the Formulas tab, click Define Name. In the New Name dialog, enter query_results for the Name and enter the following for Refers to: =Dashboard!$D$2, then click OK.

Note: If, for whatever reason, you need to modify a defined name that you have created, you can access it by clicking the Name Manager button on the Formulas tab and editing it within the Name Manager dialog. In addition to the names that you have defined, you will notice that there are other names that are needed by the Excel Add-in to work properly, which should not be altered in any way.

In the Result Destination field on the q-sheet, enter query_results.

Since we will most likely run this query more than once, let's make sure that the contents from the results of any previous run of the query are cleared before we paste the results of the current query. To do this, click Define Name. In the New Name dialog, enter query_results_range for the Name and enter the following for Refers to: Dashboard!$D$2:$E$500, then click OK.

Note: This assumes that our result data will be less than 500 rows, which is probably safe for this example; however, you would want to specify a range that makes sense for you.

In the Clear range before pasting field on the q-sheet, enter query_results_range.

We would also like the column labels to be displayed at the top of the result columns instead of the column names, so let's select "Column Labels" from the Column Headers drop-down.

Our q-sheet should now look similar to the following:

Now, let's test it out. To run the query, press Ctrl+Q.

Then, click the Dashboard tab to see the results:

You can see that the results have been pasted in our dashboard right next to our input values. In the next section, we'll see how to apply basic and conditional formatting to these results.