Excel automations

Advanced Admin, Expert Admin 

It is possible to combine the power of Excel with that of TimeTonic in order to automate your notebooks and take advantage of an almost infinite horizon of calculations.

In short, it consists in associating an Excel file with a TimeTonic table, which will perform calculations in the background and integrate the result in a table field.

The principle is simple: you can define TimeTonic fields as input values (= IN) in Excel and TimeTonic fields whose value will be the result of the calculation in your Excel file (= OUT).

Below is a concrete example to illustrate how Excel automation works.

Case study: change of status according to an amount

Here we want to perform the following operation :

If the amount before tax of a project is higher than 600,00 € then its status automatically changes to "Qualification".

First step
Create an Excel file and prepare the IN & OUT cells

Here cell B2 is used as IN for the amount before tax and cell B3 as OUT for the status. 

Note: keep in mind the name of the sheet of the workbook

Once finished save the Excel workbook

Second step
Synchronize the Excel file with the TimeTonic table

In the table options, click on the Automation option and then on Automation with Excel and download the sync file

Here we have uploaded the file called "Synchro Project".

Note : a TimeTonic table can only contain one Excel automation file at a time. Uploading one will overwrite the old one

Third step
Enter the synchronization codes for the columns

Click on the table options :

And select "organize fields" :

We want to synchronize the HT column with our Excel file, so that the content of cell B2 is replaced by the content found in TimeTonic. 

So we will modify the column "HT" and enter the synchronization code of the IN, i.e. in_Feuil1_B2 to point to the cell B2 of the sheet Feuil1 in the Excel file. 

We will then synchronize the column "Status" which will be the OUT of our file, the synchronization code is therefore the following: out_Feuil1_B3 

Result 

The automation with Excel is complete. The status changes automatically according to the price before tax entered.

We have just seen a brief overview of the possibilities offered by the Automation with Excel, these are almost unlimited, use Excel to modify dates automatically, change statuses, checkboxes, send comments and notifications, calculate margins etc.

Need a break ?

Deactivate and activate your automation in one click thanks to the dedicated option.