Create a formula dynamically

Roger

Hello,

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:

https://community.smartsheet.com/discussion/68156/formula-result-from-another-formula

=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))

where:

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)

Thanks

Paul Newcome

In row 1:

=INDEX(CHILDREN([Task Name]2), MATCH(MAX(CHILDREN([WF-Multiplier]2)), CHILDREN([WF-Multiplier]2, 0))

In the first Product row:

=INDEX(DESCENDANTS([Task Name]@row), MATCH(MAX(DESCENDANTS([WF-Multiplier]@row)), DESCENDANTS([WF-Multiplier]@row, 0))

Paul Newcome

Unfortunately what you are trying to do is not exactly possible. Are you trying to reference child rows? If so, you can use the CHILDREN function. Why does referencing the entire column not work?

Roger

I have a kind of header with "project plan" with milestones and so.

Then i have a "project development" area with different products, each product has a similar development structure, so its not fix.

Once the user creates a project out of this template, they add "products", those are the sections (several rows with some "hard coded" formulas) and they may add or remove sub-tasks from this new "product-section".

Each of this prodcut/section has a semiheader and i collect data from the subtasks into determined columns, reporting purpose.

Then, those formulas collecting or parsing data from all sheet, are not working as they will run through all products/sections and not into a specified product/section.

Paul Newcome

Are you referring to rows that are indented? Exactly what row are you wanting to put the formula in and what rows are you trying to reference with the formula?

Roger

Hello, Yes, are indented, but not only at 1 level.

I have the project Summary (Row 1) and the milestones and some tasks project related. In the column KPI-CurrentPhase and KPI-CurrentPhaseStatus, i use the formula limeted to the Project Plan, so its not parsing the products (red Lines)

To limit i use the indexes 1-30, which is fix, I would like to calculate them dynamically, but in this case, as its almost always fix, its fine

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

Then i have the same situation for each product (red line) i want to see in the header (rows:30, 56, 89) the current phase and status, from each product in particular.

Here (Row 30) i would like to show the same the [Task name] with the MAX WF-multiplier for the First product.

So I expect, in this case, to see P-PR

out of a formula like.... (which is failing :P)

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

Then the range 30 to 55 in this case (25 lines)

and the range 56 to 88 (32 lines) for next product

and so on ....

Paul Newcome

Try using a CHILDREN function for your ranges.

For the top row, use

CHILDREN([Column Name]2)

and for the Products, you can use

CHILDREN([Column Name]@row)

Roger

I dont really understand your suggestion. Use the children column inside the MAX function?

In fact, the column, WF-SubLevel is a =count(Children()), but this function is counting the "first level" of children.

I dont see how this can help 🤔

sorry, im stating to work with this tool.

Paul Newcome

In row 1:

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

In the first Product row:

=INDEX(CHILDREN([Task Name]@row), MAX(CHILDREN([WF-Multiplier]@row)))

Roger

The children function is not working as it stops at the first "level".

In addition the MAX(CHILDREN([WF-Multiplier]@row)) function will return the "rownumber", then the INDEX function will "jump" to the child number resulting from the function.

In my screenshot it will jump to the task name of the children number 33 and it actually detects 5 children (first level child)

Im sorry to bug like this :) but i can not find any way to "limit" the range.

Paul Newcome

Exactly which rows are you trying to pull from? The blue rows?

Roger

Yes, I need to pull the next blue line to be "performed" to the header, this for each section (product header) and Project Header.

Sections:

 are the Project plan (and its header is row 1)

 and each product header (red line) delimited by Index=200

The milestone or blue lines to pull is the only one "in progress" inside this range/section, also identified by WF-Multiplier > 0.

This is why, if i pick the max of the full column: it may pick a blue line from another "section/product".

So i was trying to use the range limiting it to:

from: @row

to: ... this is my problem, i can identify the row and get its rownumber, but i dont know how to use it.

Thanks again

Paul Newcome

In row 1:

=INDEX(CHILDREN([Task Name]2), MATCH(MAX(CHILDREN([WF-Multiplier]2)), CHILDREN([WF-Multiplier]2, 0))

In the first Product row:

=INDEX(DESCENDANTS([Task Name]@row), MATCH(MAX(DESCENDANTS([WF-Multiplier]@row)), DESCENDANTS([WF-Multiplier]@row, 0))

Roger

Thanks a lot @Paul Newcome .

It finally worked with:

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

The "key" was this descendants function.

Sorry for all mess :)

Paul Newcome

Happy to help! 👍️