For up-to-date Handsontable documentation, see handsontable.com/docs.

Data entered into the spreadsheet may often contain some cell dependencies. For example, you might want to make one cell’s value dependent on another. You may have asked yourself thousands of times in many cases how easy it is to create relationships between cells in Handsontable. I’ll do my best to explain it to you.

When do cells depend on each other?

You can say so whenever the value of one or more cells influences the value or attribute of another cell or cells.

When using Handsontable you have multiple possibilities. You can decide between the date, time, autocomplete and many more when choosing the type of cell. The process of creating dependencies is pretty much the same for each one. Assuming we have two cells dependent on one another:

• For cell 1 we listen for a change using a hook called afterChange.
• For cell 2, which depends on cell 1, we apply a method called setDataAtCell. We do so whenever we wish to change its value. By using setCellMeta we change its metadata (its appearance for instance).

The possibility of connecting cells in an easy way and defining their mutual relationships is a thing that makes the business world your oyster. Just use it! If you own an online business, you can decide which products are available in your store and which are not. Imagine, this is just the tip of the iceberg.

Checkbox-dependent cells

Let’s start our journey through cell dependencies with a simple checkbox example. It accepts only two states and, in my case below, the checkbox is selected by default. Whenever it gets deselected, the whole row changes its background color and becomes read-only.

In the example above, we added a class called highlightCell through the getCellMeta method to change the background color of all cells in the row, besides the checkbox column. The read-only property was also added with the getCellMeta method.

In another demo, we are going to change the value inside the cell. Selecting the checkbox erases the content of cells under the Year of production and Colorcolumns for this particular row. See how it works below:

In the example above, to erase the cell values after calling a hook called beforeChange we use the setDataAtCell method.

Summary

In the presented cases we have learned how to use checkbox states to change cell values or their attributes.

Select-dependent cells

When talking about data selected from a list of available options, we mainly refer to the dropdown cell. In the case below, by changing the car production year we determine the vehicle owner. See the example code here:

The above is possible thanks to listening beforeChange and through the implementation of methods such as setDataAtRowProp. Don’t forget to pass a specific value, like internal as the source argument in order to avoid an infinite loop in the beforeChange callback.

The next demonstration is an example of how to set a background color for the cars produced in 2016. We used a dropdown cell to present this concept.

Choose the year 2016 from the dropdown to make the whole row green. Want to see how this works? Jump in:

Once again, we used setCellData in order to add the highlightCell class, which makes the cells green.

Summary

You can use the exact same methods to change cell attributes. It doesn’t matter whether you pick select, dropdown or autocomplete type.

Date-related dependencies

The case below shows you how to change the format of a time display. Either from 12-hour to 24-hour or the other way round. See the example here:

We call a hook named beforeRenderer to transfer the time format to the getCellMeta method, which is then used in a render of a time-type column.

Summary

If you wish to change your clock from a 12 to a 24-hour one, you just need a few lines of code. You are just one step away from making an international clock displaying the current time in all the countries in the world! Sounds exciting, doesn’t it?

Content-related dependencies

This time let’s imagine we belong to a world of quantum physics and we want to present the results of particle size measurements. According to how accurate your results ought to be, you can set your own decimal place. In the example below, when typing a digit in the Fraction digits column we can specify the accuracy of the result displayed in the Value column. See how it’s done here:

The above was possible thanks to a custom function called makeFormat. It takes a value from the Fraction digits column as an argument and precisely sets the decimal place in the Value column.

In case you want to change data in a dropdown cell, use the example below. Theoretically speaking, it is viable only through accessing instance settings. Thankfully we know how to obviate that rule.

Enter the value in any cell of the Colors column and watch how they appear in the dropdown list, beside other options, in that column. It’s pretty simple, as shown below:

In the above example we enter the values as text and treat the comma as a value separator. Next, taking the entered values as a data source, we make a list of choices for the dropdown cell. In practice, we extend the renderer called AutocompleteRenderer by the possibility of getting the list of entries to the dropdown cell from its neighbour.

Summary

Text or numeric cell types don’t have to present only static values. They can be dependent on one another, but are also able to affect the fields of a different type such as dropdown.

Conclusion

What distinguishes a regular spreadsheet from your application is the power of customization. Only you decide how far you adjust Handsontable to the needs of your users and business case. The examples that we delivered will surely help you take a look at your spreadsheet in a way you might have never imagined.