Manpower Availability and Scheduled
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.
Answers
-
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.
-
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)), ", ")
-
Thank you so much!! I’m going to test this out today.
-
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.
-
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
-
Hi Theresa, I am looking to make something similar. Did you have any luck?
Thanks!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 199 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 445 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!