[New in version 10.1] Choosing the Mode on the Analytics Edge ribbon lets you decide if you want a macro function or connector function to start an Analytics Edge Macro, a Quick Query, or simply run the function once (Interactive).
This mode allows you to read data from the selected cell, run the requested macro function or connector function, then decide where to write the results.
Macro Function – if you requested an Analytics Edge function such as Pivot, Convert or Filter the add-in will read the data from the selected worksheet/cell and show it to you in the ‘ᴁ Step Results’ temporary (green) worksheet. The function will be applied to this data, and then you can save the results.
Connector Function – if you requested a function from a connector, the add-in will read the data from the selected worksheet/cell and show it to you in the ‘ᴁ Step Results’ worksheet. Any available columns are available for use as [Range Names] in the connector — for example, the value in the ‘Date’ column can be used by entering ‘[Date]’ into one of the wizard’s entry boxes:
Select Destination – after the function or query has run, the results will be visible in the ‘ᴁ Step Results’ worksheet (with a distinctive green background) and a new dialog will pop up asking where you want to write the results to.
Overwrite – choose this to overwrite the original data (if there was any) with the results. Note that there is no undo for this function, so review the results displayed behind the dialog before committing.
New Sheet – choosing this will create a new worksheet for the results. This is the default option.
Selected Cell Location – this option allows you to pick a worksheet/cell location anywhere in the current workbook. The results will be written starting at the selected cell.
Optional: if you like the result but want to repeat it with some changes, you can choose to save this read-function-write sequence as a new Analytics Edge macro — do not Overwrite the initial data if you still need it!
Available only for connector queries, selecting this option targets a new, refreshable query to be written to the selected worksheet/cell. After the query runs you will be presented with optional Quick Functions. Intermediate results are visible in the ‘ᴁ Step Results’ worksheet behind the wizard. When you are ready, click the Write to Worksheet button to write the results to the originally selected worksheet/cell location.
Analytics Edge Macro
With this option, you can enter a new macro name or simply click OK. If you don’t enter a macro name, it will be given the name of the selected worksheet/cell location (e.g. Sheet1!A1). When you click Refresh All, macros will be run in alphabetical order (except for those that start with an underscore such as ‘_prep data’). After you add the new function/query, the Task Pane will open with your new macro.