Autofill is working on some formulae in a row and not others

Rob Hagan

We have been using SmartSheet for about 3 years and have recently developed an approach for our project managers using SmartSheet. We are about to roll it out to our staff but have found an intermittent behaviour that is most troubling.

 

Sometimes, just sometimes, when we insert a new row or add a new row at the end of the used rows in our project plan one of the two formulae in the rows above is autofilled and the other is not. It is always the same formula which fails to autofill.

 

I note the posting in https://community.smartsheet.com/discussion/automatic-formula-fill where Travis states: "Has this formula autofilled on some sections of the sheet or has it never worked?

 

Check to see if you have predecessors in the parent row of the section where you are inserting the new row. If the parent row has predecessors, formulas will not autofill.

 

Also, make sure you are inserting or using a NEW row (a row that has never contained data). Used rows (even if they are blank) will not autofill. "

 

I have replicated the situation where the parent row has a predecessor and it fails to autofill both of our formulae (in line with the advice above). Fortunately, in our convention for use of SmartSheet for our project plans we have decided to not use predecessors on parents.

 

But our situation has one of the two formulae failing to autofill and the other succeeding to autofill.

 

Most grateful for any assistance.

 

Regards,

Rob.

Greg Hawk

I would reach out to support@smartsheet.com for help. There are rules which affect auto-filing formulas (like the predecessor in a parent row rule). There may be something on your sheet thats affected by one of these rules. 

Rob Hagan

Greg,

 

Thanks for responding and you are correct about the predecessor in the parent row (which doesn't appear in the published documentation on autofill http://help.smartsheet.com/customer/portal/articles/1641473). I only found out via Support.

 

I have, in parallel, been working with Support and they have only (today Melbourne time) come back with "I think I see what’s happening: because the formula in the Row Type column includes the COUNT(CHILDREN() formula, it behaves differently in a parent row versus a child row. Even though it looks the same in the sheet, Smartsheet sees them as two different formulas because of what’s happening behind the scenes. In contrast, the formula in the Task Status column doesn’t utilize hierarchy, so it autofills as expected. The solution would be to make sure you always have two children above the row you’re inserting to ensure the formulas autofill as expected."

 

Again, this is not in the documentation (http://help.smartsheet.com/customer/portal/articles/1641473).

 

Having come from an Excel background and being a newbie to SmartSheet, I was expecting that autofill would operate in all circumstances. The published documentation pulled that expectation back a bit and now the two extra restrictions (be wary of parents and don't use CHILDREN()) are biting us more in how we want to construct dynamic behaviour in our project plans.

 

I would have expected that either all the formulae would be autofilled in a new row or none of them. This situation where one fills and one doesn't will require me to put error checking the one that fills to detect when its friend is not autofilled (given that the checker does it own job and doesn't use CHILDREN() this will work). With it throwing up a result of "MISSING FORMULA" and a conditional format marking that whole row as bright red when that column contains "MISSING FORMULA" then we'll get by.

 

I do entreat SmartSheet to update its documentation with all of these wrinkles (or point me to where they are already published).

 

Thanks to Jeff in Support for turning this around in only three working days to give us clarity in pushing on with our project rollout to our staff.

 

Again, thx Greg

 

Joe Williams

That's a nice polite way of saying it. 

 

I think this situation is UNACCEPTABLE.  It's obvious in the vast 99% of cases, we always want a formula to auto-fill.  Yet smartsheet puts all these ridiculous "conditions" on whether it will do so or not, with no configuration options.  This makes the entire formula situation very brittle and unreliable.  

 

What i find unacceptable is that smartsheet thinks this is an adequate approach.  It is not.  it iwll break just about any business that attempts to rely on formulas.   You can error check until the cows come home.  That is not a scaleable and reliable approach.  eventually users will break stuff faster than you can fix it.  I've tried.  It doesn't work.

You can never rely on user actions in order for stuff to work correctly.  Users do all sorts of stuff.  Web forms get submitted.  Users add rows not necessarily beneath the last row.  That's just how people are.  

Formulas should always work (or least a sheet should be configurable to ALWAYS auto-fill, period).  This stuff can't be contigent on whether or not a user "did it right".  Thats absolute garbage.  I for one am making my displeasure obvious.  FIX THIS.  

 

Floor

Hi,

 

I come across exactly the same problem, but this problem was reported 3 years ago. Is there any way around this problem so everything will be autofilled? We are trying to set up a template sheet for all employees in which the template colour etc. is automatically done with formula's. For this we need parents and child-rows. 

Does anyone know a way around? 

Thanks in advance!