Autofill compliance

Brett Evans

User Mgmt:

I have users entering data into a sheet. They are not always inserting lines in a manner consistient with Autofill and some formulas in hidden columns are not filling in, "breaking" the sheet.

Does anyone have any strategies for making sure people follow rules that allow autofill to complete? I have trained ad naseum and I am in need of an engineered solution.

Thanks,

Brett

J. Craig Williams

Brett,

This (strategies for adoption) deserves a longer post, perhaps a book or two.

There is a large component of who's asking.

As a team leader or supervisor, I would try appealing to logic, data, vision of what the process's benefits were intended to or expected to gain.

I'm alway open to suggetions on how to do something a different way or reasons to not do something at all. But absent of those suggestions or reasons, then the process was to be followed.

When that did not work, I made it clear that the process was part of my expectations for what they were to be doing with their paid time. If they did not meet my expectations it would be reflected in pay raises, bonuses, and other areas of our supervisor-employee relationship.

As a consultant, I'm limited to examples and reasoning, without the support of any rewards/punishment system to back it up.

Here's a couple things that have worked:

Don't break the build.

If a group has a software development system that requires daily or periodic builds, then there is the chance the build is broken and no one can work.

https://nathanbrixius.wordpress.com/2011/01/07/dont-break-the-build/

There is a debate on whether this should be applauded (programmers are encouraged to experiment) or condemned (programmers are prevented by working because of the broken build).

For a Smartsheet scenario, the result of the broken build (in this case, the auto-fill of formulas or formatting) is often hard to see at first.

But it is usually fairly easy (a few minutes) to determine the culprit.

In that case, it might help to have the user become a monitor of the sheet when changes are made - set up an Alert when anything changes and make it their responsibilty to ensure the sheet is not broken again. If they complain, and they will, put a price tag on rebuilding the system each time it is broken. That simply your time to troubleshoot and rebuild, others time when the system was giving them inaccurate or misleading information.

Public shaming.

Seriously, just let everyone in the team know that Bob was the one that caused their inconvenience recently. Use this only on repeated and unrepentant users.

How are they adding rows?

Is there something in the process that can be changed?

Too many Admins is often a problem I run into. Since an Admin can edit (without warning) a locked cell (by row or column), they can easily overwrite a formula and mess things up. I have recommended to my habitual over-writers to create another Editor user for their day to day ops and leave the Admin user for special cases.

Monitor for new rows.

If auto-fill is the problem, then a column that will be a marker for ones that are successful, like a Checkbox column with =1 instead of a check should auto-populate as checked. Use Zapier (www.zapier.com) or the API to capture new rows that have that column unchecked and then add an action of your choice - an email to you for example.

There's more, but this is long enough.

Craig