Hi,
May i know how to make it auto add formula when new row added?
Example: Cell A1 have formula =B1+C1
When new row added, in cell A2 auto have formula B2+C2
Hi Sky_Quah,
We can use Auto-fill feature or Column Formula data type to achieve this, based on our requirement i.e.,
When the new row is created, select the border of the cell which has formula already defined, and then hold and drag below to fetch the expected formula result i.e., =A2+B2.
If we want to reflect formula across entire column then we can make use of โColumn Formulaโ data type.
Create a Column formula data type and enter โ=B+Cโ in formula expression editor and then click on save.
Now on, whenever the new row is added, the formula evaluates accordingly.
Hi Kiran,
I not prefer 1st solution because it need to drag manually.
I prefer 2nd solution, but i realize that it cannot calculate across other sheet?
On column formula data type, it cannot calculate Sheet2!A+Sheet2!B
Yes, Sheet2!A is not a valid variable token, hence this will not evaluate correctly.
If requirement is to reference matching row fields in a cross-sheet, you may consider writing Column-Formula expression leveraging INDIRECT function as such:
B+INDIRECT(โSheet2!Bโ&ROW())
Will sum Column-B cell on a row with Column-B cell on a matching row from another sheet i.e. Sheet2.
Alternatively, when relating rows across sheet for such computations, a more prominent SSDC way of doing it is defining an explicit relationship between the rows ( esp. when cross-sheet matching row criteria is not as simple as matching row indexes ).
In this approach, we can define a relationship column and set its value with the matching row in cross-sheet. We can pull fields from related sheet rows into the source sheet by defining lookup columns over this relationship. Once available as lookup fields in source sheet, we can use these related row fields in formula computations.
Will share a sample spreadsheet with both these approaches for reference.
1 Like