Create a formula dynamically


I would like to know if its possible to create a formula dynamically.

I want to split my sheet in various sections, but i dont know when defining my template if the end user will add or remove rows, then i can only calculate the number of rows for each section by using an auxiliary column.

Then my formulas need to consider the "number of rows" when executing or parsing.

My idea is to count the children or a column with a given value or to find a row with a fix value, then I will know the range of the section.

Once i know this range i can use it as the index.

@Paul Newcome helped me to find out this formula which works nice to find the result im looking for:

=INDEX([Task Name]:[Task Name], MAX([WF-Multiplier]:[WF-Multiplier]))

but i ended using:

=INDEX([Task Name]1:[Task Name]51, MAX([WF-Multiplier]1:[WF-Multiplier]51))

The problem is that in this section the range 1 to 51 is fix, but then in next sections is not, so i have to calculate these "indexes" to build the formula.

To calculate i can use a "[Product code]" column which is the same value for each section (and different from all other sections), so i can count the number of rows (which is different for each section) and then build the formula.

something like:

=INDEX([Task Name]X:[Task Name]Y, MAX([WF-Multiplier]X:[WF-Multiplier]Y))


Index X is the value from column [WF-Rownum]:

=COUNTIFS([Task Name]$1:[Task Name]@row, OR(ISBLANK(@cell), NOT(ISBLANK(@cell))))

and Index Y :

=COUNTIF([Product code]:[Product code], [Product code]@row)


Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!