Every retrieved cell is a different "query".
XLFetch searches left (same row) and above (same column) each retrieval cell to identify the dataset and query parameters.
The result is returned, and the cell is tagged as a retrieval cell so 'Retrieve Sheet' can be used.
Every retrieval cell needs to have:
1. a dataset name
2. a 'measure', which is usually a numerical column name to be returned
3. (optional) Any other values found in the dataset, which are used to filter the results that match the provided values
You can place the query parameters anywhere to the left and/or above each retrieval cell.
XLFetch knows the values in each dataset, so any other values in your spreadsheet will simply be ignored.
Thus existing reports can use XLFetch for data retrieval without recreating them.
Use 'Retrieve Cells' the first time you want to set a cell as a retrieval cell.
If 4 our less cells are selected at the same time Retrieve Cells will temporarily highlight the parameters used to retrieve the result, which is helpful while creating reports and debugging.
'Retrieve Sheet' will find every XLFetch retrieval cell and will return the results to those cells, so there is no need to select them manually (which would be a pain!).
For each retrieval cell XLFetch uses the parameters currently present, so the correct result(s) will be returned based on any edits or changing values.
Each retrieved cell contains:
1. 'xlfetch' identifying the cell as a retrieval cell
2. the parameters used to retrieve the result. This is for informational purposes only and is helpful for debugging.
3. the result value
Values from the same dataset column can be added together.
Query parameters can also be divided to susinctly provide common ratios like sales per unit, % year ago, etc.
'Show Data Detail' pulls the lowest level data used to calculate the current cell value and puts it in a new sheet for your review.
This allows you to drill down into your results when analyzing and using your data.
This is a matter of personal preference, but I almost always get started by typing them in and then using 'List All'.
'List All' is an easy, powerful way to pull values from one or more columns quickly, and works off of both column values and column names.
You can use 'Explore Datasets' which will show you every data set and the values in each column of each dataset, as well as the measures.
This option is most helpful if you're not familiar with your dataset and the values in it.
'Find Members' can be used to perform a wild-card search for parameters.
'Quick Pivot' provides a user interface to select a dataset, a measure, and up to two horizontal and two vertical fields.
Once you are familiar with your dataset column names and values you can leverage the state of the art Neural Linguistic Programming (NLP) interface.
Perform adhoc analyses quickly or jump-start your work creating a new spreadsheet.
'top' and 'left' indicate where to place query parameters, and there are options to sort, remove columns or rows, etc.
XLFetch can retrieve text values just like a vlookup, but:
1. can use multiple criteria (filter using values from multiple columns)
2. the returned column does NOT need to be the left-most column
The example uses data from another sheet (i.e. an 'automatic' dataset), and retrieves values based on both single and multiple columns.
Use the ribbon to specify if you want values returned in thousands ($M) or millions ($MM). Alternatively you can specify $, $M, or $MM as a query parameter to ensure other users can run your report and see the results formatted as designed.
XLFetch retrieves data asynchronously, which means you are free to move around in Excel and even change sheets while your results are being retrieved.
For longer-running operations like processing datasets XLFetch gives you the option to run those tasks in the background as well.
The 'global parameters' feature allows you to specify one or more query parameters that will be used in EVERY retrieval cell in the current sheet.
The 'global: [parameters]' directive can be put anywhere in the first 5 rows of a sheet, and it's usually most helpful if you're working with a single dataset and/or a single measure.
If you want to create two different queries in the same row or column but there are conflicting parameters you can use 'stop' to stop XLFetch as it gathers query parameters.
You can hide the stop values by either making the stop text white or by hiding the appropriate rows and columns containing stop.
It's also occasionally helpful to tell XLFetch to ignore one or more parameters.
Use the 'skip=[parameter list] to skip one or more parameters.
The default retrieval operation is to 'sum' a numerical column.
You can specify other operations using op=[operation], where [operation] is min, max, count, or avg.
