Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Formula Help

Glen Coventry
edited 12/09/19 in Archived 2016 Posts

Hi. I am a project manager for a design build company and I am creating a template to use to forcast the labour required on a job that automatically decreases based on the percentage complete.


I am wanting to add a formula to one of my sheets that will automatically populate up to 4 column cells based on an option chosen from a dropdown menu. 


The drop down will show which specific team members are required to complete a specific task ie: a Carpenter and an Apprentice out of a list of about 8 options. Once that is chosen I want the columns for Carpenter and Apprentice to be updated with the duration value multiplied by the percentage still to be completed with the column cells for the team members being left blank


These values are then totalled at the bottom of the sheet to give me a time value to complete the project which I then convert to a dollar value which is monitored against the budget


Thanks in advance




  • Christian Wells

    Hi Glen,


    How about a column for each trade where you enter the number of trades people.  Add another column with the rates for each trade (hide this column maybe)


    then calculate....the Remaining Labour and Remaining cost.  See the attached screenshot.  The formulas are below; (For row 6)


    Remaining labour

    =([Apprentice Chippie]6 + Chippie6) * Duration6 * (1-[% Complete]6) 


    Remaining cost

    =([Apprentice Chippie]3 * Duration3 * (1-[% Complete]3) * [Aprentice Chippie Rate]3) + (Chippie3 * Duration3 * (1-[% Complete]3) * [Chippie Rate]3)


    Expand it out for further columns and tradies on your project.



    A bit cumbersome, but should do the trick


    Good luck.


  • Glen Coventry

    Thanks Christian... That may work. I may not have been clear however so I will have another go...


    I have included a screen shot of what structure I have to date... What I do when I build a schedule is I look at each task and choose what labour confiruration will be required to complete that task.


    What I would like is for each of the columns for Site Foreman, Carpenter etc to be updated based on that selection showing the value in the duration column and then multiplied by the % still to be completed to give me a total at the bottom which shows day etc required to complete the project


    At the moment I have to manually enter that value into each of the PM, Site Foreman columns


    What I am trying to do is build a template that foolproof for the other PM's to use where I can lock these formulas off once correct


    Thanks again






    SS Template.jpg

    SS Template.jpg

  • Travis
    Travis Employee

    To make sure I understand,


    You select an option from Resource Allocation column, then the corresponding column (PM, Site Forman, etc) should calculate Duration * (100% - % Complete)? 


    For PM, use something like this:


    =IF([Resource Allocation]1 = "PM", Duration1 * (1 - [% Complete]1))

  • Glen Coventry

    Perfect, Thanks Travis


    And one last question... As there is the choice in that list of PM on his own or the PM and SF (PM,SF) how do I add that to the above example ( Sorry, very little excel formula experience )



  • Travis
    Travis Employee

    You could use a FIND function to locate a string in the cell. Try this:


    =IF(FIND("PM", [Resource Allocation]1) > 0, Duration1 * (1 - [% Complete]1))

  • Glen Coventry

    Perfect... Thanks heaps

This discussion has been closed.