Manpower Availability and Scheduled

Theresa Freeman

Hi,

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.

Excel Formula - Old Way to Pull Crew Availability.JPG

Crew Availability Example.JPG

Job Assigning.JPG

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)

Working

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

Available

=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

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

Hi,

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.

Example:

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

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}

https://www.smartsheet.com/contact/resource-management-demo/video?fts=rm-campaign-jan-2022

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

https://www.smartsheet.com/contact/sales?fts=contact

• Seth

RwhiteCMD

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

Thanks!