GEXS Formula

The GEXS formula opens a selector panel, with the values returned from the specified query. Selected values from the first column are then returned to Excel.

1. In Excel, select the cell where the formula is to reside (typically, adjacent to the Cell for Value), then, from the Spreadsheet Server Ribbon, select Formula Builder. The Formula Builder panel appears. Select the formula (GEXS) in the Query Formulas tab.
2. Use the following table to enter data in the GEXS panel:

Field	Description
GEXS:	Opens a selector panel, based on query results (see GEXS Formulas for more information).
Query Name	Click the Browse button to navigate to, and, select the query to be executed. Click the SAP Explorer button () to search for, and, select the SAP query (SQ00) to be executed (see SAP Explorer for more information).
Worksheet	Click the drop-down list, and select the worksheet destination for the formula. Defaults to the current active worksheet.
Starting Cell	Select the cell where the formula is to reside (typically, adjacent to the Cell for Value).
Clear/Shift Worksheet	Specify, when inserting data, whether to overwrite existing cell data in the worksheet, clear all cell data in the worksheet, insert the GEXS formula to the target cell with cell references to parameters, or, to insert the GEXS formula in the target cell, then insert parameter columns and rows to the top-left of the existing worksheet data.
Selector Text	Specify the label for the GEXS.
Cell for Value	Specify the cell destination for the selected query value(s).
Use/Parameter	If selected, the query parameter is included in the formula.



3. Click Insert. The formula is inserted into the cell, and the specified Selector Text appears in the cell. The true formula (GEXS(...)) can be seen in the Excel formula bar.

4. To execute the GEXS formula, select the cell containing the GEXS formula, then, from the Spreadsheet Server Ribbon, select Drill Down, or, right-click, and select Spreadsheet Server > Drill Down. The Parameter Selector panel appears.

5. Data in the list may be filtered, resorted and/or rearranged as necessary.
• To filter data: select the appropriate output column and type filter in the Search box. Alpha fields filter character-by-character. Numeric fields filter on entry of the full field value.
• To resort data: click the column heading to resort a column in ascending order. Click the column heading a second time to resort the column in descending order.
• To move a column: click and hold the desired column header, then drag-and-drop the column left or right, to the desired position.

Note: Column order is important, as only the first column of selected data will be inserted in the formula.

6. In the Output Format panel, specify the appropriate Parameter Type, select the desired value, then click the Insert button. The selected value is returned to the Cell for Value, cell in the spreadsheet.
7. To select:
• A single value: double-click the value, or, select the value and click Insert.
• Multiple values: press and hold the Ctrl key, as values are selected.
• Multiple values in a range: select the first value in the range, press and hold the Shift key, then, select the last value in the range.

Example

Multiple values for a field to be used by a Smart Parm are selected, and the Insert button is clicked: