Basic Concepts

This page describes the basic concepts of a SheetKraft automation.

Automation Setup

A SheetKraft automation primarily consists of an Excel workbook stored in a folder. The workbook contains formulas that define the steps of the automation. Sample input data files (if any) are placed in the folder and the formulas in the workbook load the sample data. Alternatively, the formulas may load data from external sources such as a database. The loaded data can then be transformed / aggregated / processed in various ways using more formulas. Finally, results may be exported to a database or to files in various formats. All commonly used formulas have dedicated graphical interfaces that are launched from buttons on the SheetKraft ribbon.

The formulas form a chain of calculation where the results of one formula can flow into other formulas. Clicking the Run Book button on the ribbon runs all the formulas in the correct sequence and produces the results. The formulas are setup and tweaked until the automation produces the correct results for the sample inputs. Any variations or exceptions to general rules are also captured as formulas and tested.

The basic idea is that the data can be different in each run but the formulas that define the logic remain the same. SheetKraft formulas adjust to variations in the number of rows of data and hence the same formulas can deal with varying data.

Formula Creation

A typical formula editing interface has an initial data range selection windows. For example, clicking on the Filter button prompts for the selection of the range to be filtered.

Range Selection Window

RangeSelection

While this window is open, a range can be selected from the current Excel worksheet using either the mouse or the keyboard. Keyboard shortcuts like Ctrl + Shift + Down can be used to select the range quickly. However, Excel's scrollbars and sheet names are inactive and cannot be clicked. To scroll, use the mouse wheel or the Arrow / Page Up / Page Down buttons on the keyboard. To switch sheets, use the Ctrl + Page Up / Down keyboard shortcuts or use the Change Sheet drop-down button. Once the range is selected, SheetKraft tries to guess whether the range has headers or not. If the guess is incorrect, the My data has headers checkbox can be manually checked or unchecked. Setting this correctly is important for subsequent steps.

Once the range selection is complete, the main window opens where various functionalities available for the formula can be configured. The range selected in the initial windows is usually shown at the top of the window in a range selection box.

FilterRange

Note that if the My data has headers checkbox was checked on the range selection window, the range shown here excludes the header row and the Headers above data checkbox is checked. Also note that the range is shown as RegionDown.SK(...). This occurs if the range selected is a continuous region of cells with no blank rows in between and one or more blank rows immediately below it. This ensures that when the source range changes (because it is produced by another formula), the changed range is used instead of a fixed size range.

Clicking on the text of the range navigates to the selection in Excel without closing the window. Clicking on the button to the right of the range hides the current window and re-opens the window for range selection. The re-opened window has an additional option to control how the range is interpreted. This option can be used to change the default RegionDown to a fixed range (as raw range:...) or to RegionRight or RegionAcross if necessary.

RangeSelection2

Main Window

The main window has user interface elements specific to each function. For example the interface for Filter has options to specify filter conditions that will be evaluated for each row of the data. Some of these elements may also have range selection boxes. These work in the same way as described above. There is a Next button at the bottom of the main window. This should be clicked once all the configuration is complete.

Formula Location Window

On clicking Next, a final window appears that prompts the user for the location in the sheet where the formula will be inserted. This location must be blank. Most SheetKraft formulas are array formulas, i.e, they occupy more than one cell. Typically, the number of columns in the array formula is fixed but the number of rows depends on the data. Hence, the location should typically not be below or above another SheetKraft formula. Otherwise, there is a risk that one of the formulas does not have enough blank rows to expand when required. In other words, multiple formulas in the same sheet should be placed from left to right instead of top to bottom.

When a formula is inserted, it is automatically evaluated and the results are shown on the sheet. The size of the array formula is automatically adjusted based on the data.

Formula Editing

An existing SheetKraft formula can be modified by first selecting a cell containing the formula and then clicking the Formula Explorer button on the SheetKraft ribbon. This reloads the interface for editing the formula configuration. The initial Range Selection windows and the final Formula Location windows are not shown when editing an existing formula. This is because the ranges and formula location are already known.

Running the automation

The entire automation can be run by clicking the Run Book button on the SheetKraft ribbon. This re-calculates every formula in the workbook. Any errors or warnings generated during the run are shown on a Calculation Log window which also shows the progress of the calculation. This window closes automatically if there are no errors or warnings. Clicking on an entry in the log navigates to the cell that produced the log entry. The log window can be closed to make any changes to correct the error or warning if necessary. The log window can be re-opened by clicking the Show Log button on the ribbon. All the entries generated during a run are preserved until the next run or until the workbook is closed.

It is also possible to re-evaluate a single array formula by clicking on the Formula button next to the Run Book button. Note that this evaluates the formula in isolation without evaluating any other formulas whose results flow into this formula (precedents). Any other array formulas whose results depend on this formula (dependents) are also not re-evaluated. This means that the displayed results may be in an inconsistent state when using the Run Formula functionality. However, this functionality is very useful during implementation as it saves the time required to run the entire workbook. Also note that making changes to an existing formula re-evaluates that single formula and not any precedent or dependent formulas.

Modifying formula locations

Formulas can be moved within a sheet or across sheets by cutting and pasting (Ctrl+X and Ctrl+V). However, because most SheetKraft formulas are array formulas, care must be taken to cut the entire range containing an array formula. Inserting rows or columns at places that do not intersect any array formula is also possible and formulas normally remain valid after these operations. However the displayed values might change to 0. This happens because SheetKraft stores cached formula results against cell references and the cache does not get updated when formula locations change. This can only be corrected by running the entire book or the affected formulas.