Allocation percentage disappears

CycleBagEd · 01/18/22

I am setting up a template for project management.

We plan in hours. I have found that the Allocation column will take a formula and have one set up to convert the hours into an allocation percentage based on start and end dates. So far, so good.

I then note that this doesn't work on parent fields. This makes sense, and isn't a problem for my (locked down) parent rows which provide roll up for project structure.

Where I do run into trouble though is that users will be able to INDENT within the parent rows I have set up, making their own parent rows (let's call them level 2 parents, having level 3 children). I have controlled for this in column formula other columns by use of things like ANCESTOR, DESCENDANTS and IF formulae and a helper column showing me the indent levels. But, on the % allocation column the formula plain disappears when a row is indented. The same formula in a row not assigned the job of "allocation" in the project settings works fine, so this is clearly programmed behaviour.

Again, this wouldn't be a big deal expect for the fact that the users CAN allocate hours on a row and then turn it into a parent, essentially becoming a parent row with its own time which then doesn't get included anywhere in the allocations.

So what do I do?

a) ignore it - tell people not to indent rows themselves and hope for the best

b) run a workflow which deletes hours and assigned staff to any row which becomes a parent to scare them out of doing it? (When CHILDREN becomes greater than 1, delete the hours and assigned cell?)

c) set an alarm column to warn them either to remove the time and allocated staff from a parent column or outdent it, coupled with a workflow?

d) ban INDENTING? - I can't see anywhere that this can be controlled.

e) something else?

Ideas welcome!

CycleBagEd · 01/21/22

Thanks @Genevieve P.

For future users, what I have done is as follows:

a) I've given up on same-column roll-ups for data entry columns. So, if someone wants a summary of e.g. hours this now appears in a totals column. This saves some really nasty formulae, and the column can be locked

b) Unlocked my locked parent rows. Users now have freedom to indent and outdent. Parent rows are unlocked, but system columns and locked formula columns remain inaccessible

c) Implemented conditional formatting. Indents are now very visible via a ROYGBIV format applied to the whole row (if there are children) or the start date (for the children with no descendants. This will help users understand what's going on

d) Implemented a conditional format on parent rows which have e.g. hours and costs applied that I would rather exist at a child level. Whole row goes red. This should work and discourage people from adding data to parent rows which will mess the allocations up.

thanks

Ed

Genevieve P. · 01/21/22

Hi @CycleBagEd

There currently isn't a way to prevent users who have Editor or higher permissions from adjusting hierarchy in a sheet, other than locking those rows completely.

I would definitely suggest educating your shared users on your current sheet set-up and how you are using indents and hierarchy so that they understand how to edit rows. You could also potentially send an Update Request to the user who made the modification (using the "modified by" system column to find the email).

Based on the account associated with your Community profile, it looks like you have access to booking Pro Desk sessions: https://www.smartsheet.com/pro-desk. These are 30-minute screen share sessions where you can show your set-up to receive coaching on best practices. At this point I would suggest that a screen share would be the best way to receive advice or ideas on this topic.

Cheers,

Genevieve

CycleBagEd · 01/21/22