New Data not automatically subject to formulae

Craig Lemberger · 10/07/20

I have a sheet with hierarchies (up to two). There are multiple formulae doing their thing across; nothing fancy: sum and multiplication. As new data come in using a form, the new data are not subject to the formulae. It looks like I have to drop down and apply each formulae to the new data. This is cumbersome and seems wrong since I have to be aware that the parent rows are subject to a different set of formulae.

Marta Lewis · 10/07/20

@Craig Lemberger Are you aware of the new column formula capability?

I'm starting to combine my formulas to take advantage of it, just to avoid MIA formulas like you describe. One formula for the whole column, to take everything into account. You'll need a level-tracking column to make this work.

Previously, in one of my 'easier' columns, level 1 was empty/did not have a formula, level 2 presented =PARENT([Task Name]@row), and level 3 presented =PARENT(Stage@row).

Now I'm using the following combined formula, enforced for the whole column:

=IF(Level@row = "1", "", IF(Level@row = "2", PARENT([Task Name]@row), IF(Level@row = "3", PARENT(Stage@row))))

Haven't tried this yet with my more complex formulas, but maybe this will work for you.

Craig Lemberger · 10/08/20

Thank you. Yes I used the column formula. It's great. You can enable and then disable. After disabling, it leaves the formulas there and then you can edit any rows you wish to not have the formula.

As regards the rest of your comments, I think that is above my capabilities at this point. :)

Marta Lewis · 11/12/20

https://community.smartsheet.com/discussion/comment/265309#Comment_265309

@Craig Lemberger, did you ever figure out the issue? If not, please share a screenshot (with confidential info blurred) of your sheet.

WRT my comment above, if I can create formulae to take into account every hierarchy level, you can too!

New Data not automatically subject to formulae

Answers

Categories