Formula Assistant

Formula Assistant is a tool used to aid in quickly creating formulas within an Executive Dash spreadsheet.

Currently the Formula Assistant is applicable for EXQ, EXD, EXS, and EXI formulas.

1. In Excel from the ExecDash menu, select Formula Assistant. The Formula Assistant panel appears, open to the EXQ tab -or- if Formula Assistant was launched while on the Executive Dash formula, the panel opens the corresponding formula tab.

Nav Tip:This panel may also be accessed by selecting the EDash toolbar or ribbon equivalent (see EDash Toolbar or EDash Ribbon).

2. Select the tab of the desired formula. The selected formula panel appears.

3. Specify the query to be used in the formula.

· When the query resides in a Query Catalog, use the drop down list to select the query to be executed.

· When the query resides in the File System, click the Open From File button ( ) to navigate to and select the query to be executed.

· When the query resides in Query Exchange, click the Search Online button ( ) to navigate to and select the query to be executed (see Download Queries from Query Exchange).

Note: After a query is installed from Query Exchange then it becomes available from the File System.

4. Use the table to enter data on the EXQ tab of the Formula Assistant panel.

Field Description

EXQ: Returns a single value from the EXQ (Summary) portion of the query into the spreadsheet. Refer to Creating and Using EXQ Formulas for more information.

Total Field If the EXQ summary field is to be defined within the formula, use the drop down list to select the field to be summarized.

Total Type If the EXQ summary field is to be defined within the formula, use the drop down list to select summary type (i.e. SUM, COUNT, MIN, MAX).

Parameters If applicable, specify the cells containing parameters for the query.

Note: Values to be entered in the EXQ formula will vary based upon whether the EXQ summary field is

defined in the query or the formula.

5. Use the table to enter data on the EXD tab of the Formula Assistant panel.

Field Description

EXD: Directly inserts the Detail results of the query into the spreadsheet.

Refer to Creating and Using EXD Formulas for more information.

Worksheet From the drop down list select the worksheet destination for the output.

Defaults the current active worksheet.

Starting Cell/Range/Table Specify the upper-left corner of the output target. This value may be a single cell, a table or a range. If Include Column Headings is selected, then it represents the left-most column heading as defined in the query.

Otherwise it represents the left-most cell of the first row of data.

Fill Down Columns Specify the number of columns that contain formulas/values to the right of the results grid. These columns will fill down to match the number of records in the results. Valid values are 1, 2, 3, etc.

Note: When using Fill Down Columns targeting a range the fill down formulas/values must reside outside of the output range.

Note: When Include Column Heading is selected, the fill down columns are required to be on the first row following the headings. When Include Column Headings is not selected, the fill down columns are required to be on the first two rows for the EXD data.

Note: In some instances, the EXD Row function may be used in place of fill down columns. Refer to EXD Row Function for more information.

Include Column Headings Select the check box to indicate that generated output data will contain column headings from the query. Do not select the check box if column headings will be manually entered in Excel.

Auto Fit Column Sizes Select the check box to have the system perform the Autofill Column Width feature of Excel when the formula is executed.

Clear Sheet/Range/Table Select the check box to have the system clear the entire output sheet (specified in the Worksheet field) or range or table (specified in the Starting Cell/Range/Table field) to be cleared when the EXD formula is executed. Do not select this check box if the EXD formula or any of the parameters for the query are located on the output sheet.

Note: See Clear a Range of Cells for clearing a range of cell data.

Field Description

Clear a Range of Cells Specify the range of cells to be cleared prior to executing the EXD formula.

Note: If Fill Down Columns are used, be sure to exclude the first row from the clear range. If the first row is cleared then the formula/value to be filled down will be deleted and blanks will be filled.

Example: When a Fill Down Column is specified, if the Starting Cell is A7, the Clear Range of Cells should be "A8:xx".

Parameters If applicable, specify the cells containing parameters for the query.

6. Use the table to enter data on the EXS tab of the Formula Assistant panel.

Field Description

EXS: Opens a selector window with the values returned from the query.

Refer to Creating and Using EXS Formulas for more information.

Selector Text Specify the label for the EXS.

Cell for Value Specify the cell destination for the selected query value(s).

Parameters If applicable, specify the cells containing parameters for the query.

7. Use the table to enter data on the EXI tab of the Formula Assistant panel.

Field Description

EXI: Opens the Detail portion of the query and displays it in the

ResultsContainer window. Refer to Creating and Using EXI Formulas for more information.

Parameters If applicable, specify the cells containing parameters for the query.

8. After the appropriate data has been entered and verified, click one of the following buttons.

· Copy to Clipboard -- copies the formula to the Windows clipboard.

· Paste to Excel -- inserts the formula to the previously selected cell on the current worksheet and closes the panel.