Formula in % Allocation Column?

Guy Behanna

I am certain this has been asked either currently or in the past, but is the capability to include a formula in the % Allocation column on the product roadmap? Right now, I have a column entitled "% Alloc Calc" next to the "% Allocation" column to calculate the % Allocation for my users. The users must ensure that both values are the same. I even have a validation and conditional formatting to indicate with the two values are not the same. Would be much easier for me and my users if I could just enter the formula directly into the % Allocation column and lock it down.

Alejandra

Hi @Guy Behanna,

Formulas can't be used in columns in fields being used for resource management, but this would be a great opportunity to submit a Product Enhancement Request.

If needed, more information on this can be found here: https://help.smartsheet.com/articles/1346969-resource-management-allocation

Alejandra

Hi @Guy Behanna,

Formulas can't be used in columns in fields being used for resource management, but this would be a great opportunity to submit a Product Enhancement Request.

If needed, more information on this can be found here: https://help.smartsheet.com/articles/1346969-resource-management-allocation

Guy Behanna

Hi, @Alejandra! I already submitted a Product Enhancement Request, but also wanted to post this question to the community to see if anyone was doing something else to solve this. Thanks!

Ezra

As a temporary solution, I also have a helper column(s) that contains formula(s), then I use conditional formatting to highlight the fields that are not yet correct.

This is still too much "work" for managers.. I guess they don't really want to have to manage the projects.

Kara Lumley

@Guy Behanna @Ezra I'm interested in hearing your use cases for this, and will provide some updates.

Please feel free to reach out to me and we can set up a few moments to chat.

Kara.Lumley@smartsheet.com

Thank you so much!

@Kara Lumley

Guy Behanna

@Kara Lumley - thanks for offering to discuss this use case. My schedule is relatively flexible right now, so please let me know when you have time to chat. Also, let's invite others you know who may be trying to solve the same problem.

Looking forward to it!

Thanks,

Guy

Dominique Rose Winther

@Kara Lumley I just want to chime in on the use cases here. I am in a professional services company and we charge by the hour. Therefore, we calculate everything in hours. So I have a column where I place the number of hours we would allocate to the task and I want the % Allocation to update based on the number of days we've put for duration. No one can calculate that a 5 hour task would actually be a 16% allocation over 4 days. So I cannot use the resource management function unless the % Allocation is entered correctly. That percentage is not useful to the teams - they want to know the number of hours they have to complete the task.

This is why it would be very useful to have a formula in the % Allocation column - then we could just have the teams enter the hours and the % Allocation would update automatically.

Thanks,

Dominique

Andrew Stills

I have a similar need as Dominique for this feature. In my best case, I could enter the duration in hours and define start and ending dates and have the allocation automagically calculate the %.

To that point, Dominique, if one could enter the start and ending dates manually, you wouldn't need both the hours and the days column.

This feature shouldn't be much different to configure than the auto fill feature of the start/ending/duration columns. If certain fields are filled in the other automatically calculates.

I could create these functions on my own... but then I break the resource part of the tool.

Thanks,

Andrew

SoS | Dan Palenchar

You can turn off resource management and then add your formula, though you lose the ability to utilize resource views this way and need alternative solutions like a resource dashboard with reports, etc.

Guy Behanna

I believe we now have the ability to add a formula to the % Allocation column. So, I added an "Hours" column for the resources to add their hours, added a formula to the % Allocation column and then hid the % Allocation column to avoid confusion. The formula is easy: =Hours@row / ([Duration]@row * 8). Note that we are not in sales, so our hour/day can be a standard 8 hours and data accuracy is not critical. Everything appears to be working now.

Andrew Stills

@SoS | Dan Palenchar , I've toyed with building out my own resource tool for just these very reasons. I'm leaning more and more in this direction as I come across more limitations of the standard offering and am reluctant to request Control Center (after just spending 9 months convincing my company to get SS Enterprise) and not knowing what 10000ft actually comes with.

@Guy Behanna , thanks for the note. I'll definitely be experimenting on this in the next couple days.

Andrew Stills

@Guy Behanna I've been trying to enter a formula in the allocation column as you suggested. It will not accept the formula if the allocation column is part of the resource management. Is this the same for you? Or, have you turned of resource management? Would love to know how you got a formula into the allocation field with resource management active if that is the case.

thanks!

Guy Behanna

@Andrew Stills - I just checked a resource view for a specific project plan (sheet) with the calculation in the % Allocation field, and the calculated percentage in the field is showing up in the resource view for each resource assigned to an activity on that sheet. Past and future assignments are appearing on the resource view. Not sure what I am doing differently, but I can confirm that the calculation in the % Allocation field appears to be working as intended.

Josh Hunsaker

@Guy Behanna & @Andrew Stills

I'm running into the same issue. I have resource management on, I can enter a formula into the % Allocation column, but after some amount of time, SS wipes out the formula and keeps the values. I've been able to reapply the formula to get around this but it would seem that there is still a bug.

The only consistency that I can find is that when one of the resources is over allocated and the sheet saves, this seems to be when the formula is wiped out.

SoS | Dan Palenchar

@Guy Behanna @Josh Hemsath @Andrew Stills

RE: Formulas in Allocation column, according to https://help.smartsheet.com/articles/765737-project-sheet-columns-start-date-end-date-duration-complete-and-predecessors

NOTE: Formulas aren't allowed in the columns listed above when dependencies are enabled.

I have not tested to see if this has changed recently. @Guy Behanna does your test have dependencies enabled?

RE Control Center/ 10,000 feet: 10K feet is meant to really handle resource management more intricately, I felt the integration with SS was pretty limited, explored it with a client about 1-2 months ago, but they have added some key features since them specifically being able to embed 10K feet pages in SS dashboards and add primary column info from SS to 10K feet, so it may be more worthwhile to pursue though I have yet to fully revisit it myself. Control center is, as I understand, more for managing projects as scale and system wide bulk changes/project roll out as opposed to intimate resource management.

Guy Behanna

@Dan Palenchar @Josh Hemsath @Andrew Stills

Please note that it is absolutely possible that I may be missing something fundamental with my portfolio setup, giving me the illusion that everything is working properly. To be completely honest, I have not had a chance to conduct a thorough test.

With that said, I have Dependencies and Smartsheet Resource Management enabled along with a column formula in the Allocation % column (screenshots below). As I mentioned before, everything appears to be working properly as I conduct a brief test of the effects of a resource view after changing resource data within a project plan that includes a column formula for Allocation %.