Manpower Availability and Scheduled



I need assistance with setting up a sheet showing manpower assigned to a job and/or manpower available. Example attached. We have one sheet that list each project and we then assign crew members to each line/job. There could be multiple crew members assigned to one job (line) I want to be able to look at a Manpower Sheet to see the list of crew guys and who is working, what job they are working on, who is off ( meaning not assigned to a project), and who assigne dto multiple jobs at the same time. We have over 300 installers in the field. I've attached the old way in excel for you to view. Any assistance would be greatly appreciated.


  • Devin Lee
    Devin Lee ✭✭✭✭✭

    Hey @Theresa Freeman ,

    My first thought is to set it up this way. With Full Crew using the formula below. You can obviously add more columns for additional crew #.

    =JOIN([Crew Foreman]@row:[Crew 1]@row, ", ")

    Then in another sheet set it up like this with these formulas. (I put them on the same sheet so you can see the references)


    =JOIN(COLLECT([Full Crew]:[Full Crew], [Start Date]:[Start Date], Date@row >= @cell, [End Date]:[End Date], Date@row <= @cell), ", ")


    =JOIN(COLLECT([Employee 1]$1:[Employee 5]$1, [Employee 1]@row:[Employee 5]@row, 0), ", ")

    Employee #

    =IF(CONTAINS([Employee 1]$1, $Working@row), 1, 0)

    Odd things that might need fixing is Working will list a name multiple times if they are working on more than one job that day and it could be worth altering the 1/0 option to go higher based on how many jobs they work that day.

  • Devin Lee
    Devin Lee ✭✭✭✭✭

    Realized the working days are missing from the sheets. Here is an update to add that in.

    Working Days - Drop Down (Multi Select)

    Work Day Conversion - This is used to convert the days into numbers which is needed for the WEEKDAY formula in the other sheet.

    =IF(HAS([Work Days]@row, "Sunday"), "1", "") + IF(HAS([Work Days]@row, "Monday"), "2", "") + IF(HAS([Work Days]@row, "Tuesday"), "3", "") + IF(HAS([Work Days]@row, "Wednesday"), "4", "") + IF(HAS([Work Days]@row, "Thursday"), "5", "") + IF(HAS([Work Days]@row, "Friday"), "6", "") + IF(HAS([Work Days]@row, "Saturday"), "7", "")

    In the second sheet

    Update the Working column to the following to include the Working Days into the equation.

    =JOIN(COLLECT([Full Crew]:[Full Crew], [Start Date]:[Start Date], Date@row >= @cell, [End Date]:[End Date], Date@row <= @cell, [Work Day Conversion]:[Work Day Conversion], CONTAINS(WEEKDAY(Date@row), @cell)), ", ")

  • Theresa Freeman

    Thank you so much!! I’m going to test this out today.

  • Theresa Freeman


    I'm finally getting back to trying to make a manpower report and i'm struggling.

    the solution above isn't working for me so i'm wondering if this helps with understand what i'm looking for:

    Sheet 1 - job name, location, start date, end date, foreman, crew 1, crew 2 crew 3 and so on.

    In each of the Columns (foreman, crew 1, crew 2, etc) have a drop down selection of pickable names which is the same list of names in each column.


    Job name Location Start End Foreman Crew 1 Crew 2 Crew 3 Crew 4

    JCpenney Green Bay 2/7 2/15 Tom Ashton Paul George Blank

    JCPenney Lafayette 3/14 4/17 Paul Tom Craig Adam Mark

    Sheet 2 - On the manpower sheet i want the first column to list every Crew member's name that is a pickable option on sheet 1.

    Second column will be start date and third column will be end date

    Formula help: if tom is selected in any row, on sheet 1, copy the start date, end date, and project from sheet 1 on to sheet 2

    Sheet two is in Gannett view so you can see which crew members are assigned and who is available.

    This information gets discussed and evaluated weekly. We are looking at project and manpower for the next 4 weeks and some tims farther out depending on the project backlog.

    Im uncertain if this should be a report or kept in sheets. I'm new to smart sheet and this is extremely important part of switching over my team from using excel to Smartsheet.

    I'll take all the help i can get to get this setup and working. Thank you in advance for your time and efforts.

  • Seth Morth
    Seth Morth ✭✭✭✭✭
    edited 02/05/22

    Hi Theresa!

    There are certainly ways to configure the base version of Smartsheet to do most of what you are asking but you will likely run into a lot of continual manual adjustments. A super-advanced user may have more insight for you, but as a fairly decent user I gave up after realizing how much work it would be to create as well as maintain/operate and started looking at the Resource Management add-on from Smartsheet. There is an extra charge, though it is more than you want it to be it is far less than any other platform to develop something so slick, and it is VERY slick! Watch this video - your team will be WoWeD! {I am not being compensated in any way for recommending this to you, nor was I asked to make the recommendation - simply a comment based on preserving sanity}

    If you are serious, contact your Client Development Manager with Smartsheet for more info, or hit up this form:

    • Seth
  • RwhiteCMD

    Hi Theresa, I am looking to make something similar. Did you have any luck?


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!