Serial Number


SerialNumber.SK function is used to get a list of numbers which has a start value and an end value.

Sheetkraft contains the function in the stored functions list. We have to type it out in a cell to use it. (See Figure)

Arguments to be passed in the function:

The following guidelines describe how SerialNumber.SK can be used.

Step 1:

Type out the formula and then click on the fx button (circled in the figure below).

Step 2:

A dialogue box appears once you click on the fx button. This particular dialogue box indicates the function arguments to be passed for the function to work properly. (see figure)

Step 3:

We are required to fill two arguments:

Start Number: The number with which you want to start the sequence.

Rows: The number of rows you want to fill with this particular sequence.

The rows are filled in an incremental manner from the start number.



Case 2: Using countdown.SK with SerialNumber.SK.

Follow Step 1 from Case1.

Step 2:

Instead of manually typing in the Rows argument, we can use the countdown function for it.

This function is very helpful to assign an “ID” column to a dataset.

CASE 3 : Using condition to specify start number

Follow Step 1 from Case 1.

Step 2:

Using the IF formula in the start number argument will check if the condition passed in the formula is satisfied first and only then the start number will be taken into account. This helps in using when and only when we have the desired conditions for a setup. In this example we have an example of checking if the dates match. (See figure)

The same procedure can be followed for other conditional formulas whenever seen fit.

USE CASE: Automated Indexing

While inserting inputs from a user to a database, we might have to assign an ID column which should be auto-generated. Each ID number assigned to the input must be unique.

Let us look at an example of how this can be done.

Step 1:

Create a sample database using Database explorer. Make sure it has the ID column along with the required columns.

In the figure below, you can see that I have created a sample database with the table name sample db. It consists of an ID column along with other columns. The database explorer will create an empty table with these particular column names.

Step 2:

Import only the ID column from this table. This will be used as a reference in our SerialNumber.Sk formula. The conditions for the filter should be maximum rows 1 and to be arranged in descending order.

On doing this, there will be a warning prompt which says No rows found in database and hence we will get a #NULL! value as an output. (See figure)

Step 3:

We will be using the output from Step 2 as a reference in the Start number argument of our SerialNumber.SK formula.

The different formulas used in the arguments of SerialNumber.Sk:

StartNumber: Using IFError in the start number argument enables us to do so.

Rows: Using CountDown.SK

Once this formula has been setup, every time the user inputs a new value and the activity is run, the ID column generates a new value with respect to the latest ID value imported from the database.