Good morning, firstly thankyou for taking the time t read this, secondly, i hope you can help.
I run 2 reports weekly to gain a monthly figure on attendance to the workplace, in the nature of this, a person may have more skills that are needed elsewhere for a few days, or may only be in my workplace for a couple of days . So that person might appear week 1 and again week 4 but not in weeks 2 or 3, someone else may come in their place to keep the company numbers at the agreed rate.
so
issue one, count individuals currently on site
issue two, capture new people entering site
currently im using =COUNTIF($B2:$B1502,"space invaders ") and the same formula to track a persons name in the aim to track how many people are on site from one particular contractor, but that only works if i know the contractors identity . if a new contractor appears and i dont know until i run the report. I guess the question im asking is, is there anyway of automatically adding a new person and a new company.
Image of first report below
The reports are placed in their own sheets Code used to populate master sheet is =COUNTIF($B2:$B1502,"space invaders ") and the same for the persons name
=SUMIF($CS$2:$CS$1500,$CS2,$CQ$2:$CQ$1500) is used to track how many notices are issued by each company
=SUMIF($CS$2:$CS$1500,CV2,$CT$2:$CT$1500) to consolidate and remove duplicates
All help supplied by AhoyNC
The aim is to have a sheet like the one directly below , hopefully there is a way to automatically add people and companies to the sheet
This all works until people disappear and new people appear
Second report i run is a notice report, where we ask individuals to partake in a safety campaign.
I run 2 reports weekly to gain a monthly figure on attendance to the workplace, in the nature of this, a person may have more skills that are needed elsewhere for a few days, or may only be in my workplace for a couple of days . So that person might appear week 1 and again week 4 but not in weeks 2 or 3, someone else may come in their place to keep the company numbers at the agreed rate.
so
issue one, count individuals currently on site
issue two, capture new people entering site
currently im using =COUNTIF($B2:$B1502,"space invaders ") and the same formula to track a persons name in the aim to track how many people are on site from one particular contractor, but that only works if i know the contractors identity . if a new contractor appears and i dont know until i run the report. I guess the question im asking is, is there anyway of automatically adding a new person and a new company.
Image of first report below
The reports are placed in their own sheets Code used to populate master sheet is =COUNTIF($B2:$B1502,"space invaders ") and the same for the persons name
=SUMIF($CS$2:$CS$1500,$CS2,$CQ$2:$CQ$1500) is used to track how many notices are issued by each company
=SUMIF($CS$2:$CS$1500,CV2,$CT$2:$CT$1500) to consolidate and remove duplicates
All help supplied by AhoyNC
The aim is to have a sheet like the one directly below , hopefully there is a way to automatically add people and companies to the sheet
| Company | ready not ready Received | No of Operatives | Required/ Over Expectations | Outstanding |
| Space Travel 1234 | 19 | 4 | 8 | -11 |
| Underworld Travel 1234 | 0 | 1 | 2 | 2 |
| Overworld extreame | 15 | 21 | 42 | 27 |
This all works until people disappear and new people appear
=SUMIF($CS$2:$CS$1501,$CS2,$CQ$2:$CQ$1501) |
| Weekly Summary for Week Commencing Sat. 15-Feb-2020 | |||||||||
| Space Travel 1234 | |||||||||
| Name | Organisation | Sat | Sun | Mon | Tue | Wed | Thu | Fri | Total |
| mickey mouse 1 | Space Travel 1234 | 00:00 | 00:00 | 08:06 | 08:15 | 08:19 | 08:23 | 08:00 | 41:05:00 |
| mickey mouse 2 | Space Travel 1234 | 00:00 | 00:00 | 08:14 | 08:29 | 08:22 | 08:25 | 08:09 | 41:41:00 |
| mickey mouse 3 | Space Travel 1234 | 00:00 | 00:00 | 08:14 | 08:41 | 08:22 | 08:25 | 08:08 | 41:53:00 |
| mickey mouse 4 | Space Travel 1234 | 00:00 | 00:00 | 00:00 | 00:00 | 00:00 | 01:50 | 00:00 | 01:50 |
| Total time: 126:31E | |||||||||
| Average hours per day: 18.1 | |||||||||
| Number of unique staff that attended site: 4 | |||||||||
| Average staff per day: 2.3 | |||||||||
| Average staff per week day: 3.2 | |||||||||
| Underworld Travel 1234 | |||||||||
| Name | Organisation | Sat | Sun | Mon | Tue | Wed | Thu | Fri | Total |
| Orc Minis Terif | Underworld Travel 1234 | 00:00 | 00:00 | 05:48 | 00:00 | 00:00 | 00:00 | 00:00 | 05:48 |
| Total time: 5:48 | |||||||||
| Average hours per day: 0.8 | |||||||||
| Number of unique staff that attended site: 1 | |||||||||
| Average staff per day: 0.1 | |||||||||
| Average staff per week day: 0.2 | |||||||||
| Overworld extreame | |||||||||
| Name | Organisation | Sat | Sun | Mon | Tue | Wed | Thu | Fri | Total |
| Goblin 1 | Overworld extreame | 00:00 | 00:00 | 00:00 | 00:00 | 00:00 | 08:49 | 05:34 | 14:24 |
| Goblin 2 | Overworld extreame | 00:00 | 00:00 | 07:56 | 07:57 | 08:02 | 08:01 | 05:10 | 37:08:00 |
| Goblin 3 | Overworld extreame | 00:00 | 00:00 | 07:52 | 07:46 | 08:00 | 07:56 | 05:06 | 36:42:00 |
| Goblin 4 | Overworld extreame | 00:00 | 00:00 | 08:42 | 08:53 | 08:46 | 08:49 | 05:33 | 40:46:00 |
| Goblin 5 | Overworld extreame | 00:00 | 00:00 | 08:41 | 00:00 | 08:32 | 08:06 | 00:00 | 25:20:00 |
| Goblin 6 | Overworld extreame | 00:00 | 00:00 | 08:51 | 08:51 | 08:43 | 08:45 | 05:32 | 40:44:00 |
| Goblin 7 | Overworld extreame | 00:00 | 00:00 | 08:24 | 08:34 | 08:17 | 08:10 | 05:25 | 38:52:00 |
| Goblin 8 | Overworld extreame | 00:00 | 00:00 | 07:14 | 00:39 | 00:00 | 00:00 | 00:00 | 07:53 |
| Goblin 9 | Overworld extreame | 00:00 | 00:00 | 08:41 | 08:05 | 08:12 | 08:06 | 05:25 | 38:31:00 |
| Goblin 10 | Overworld extreame | 00:00 | 00:00 | 07:58 | 07:55 | 07:53 | 08:03 | 05:00 | 36:50:00 |
| Goblin 11 | Overworld extreame | 00:00 | 00:00 | 08:06 | 08:40 | 08:27 | 08:26 | 05:27 | 39:08:00 |
| Total time: 356:23 | |||||||||
| Average hours per day: 50.9 | |||||||||
| Number of unique staff that attended site: 11 | |||||||||
| Average staff per day: 6.7 | |||||||||
| Average staff per week day: 9.4 |
Second report i run is a notice report, where we ask individuals to partake in a safety campaign.
| Report ID | Business | Region | Contract Number | Contract Name | Submission Date | Time | Type of Report | Name of User | company of user |
165767 | Space Travel | Mars | ******* | the Moon | 22/01/2020 | 09:48:22 | Ready | Mickymouse 1 | |
165805 | Space Travel | Mars | ******* | the Moon | 22/01/2020 | 11:37:14 | Not Ready | Mickymouse 2 | |
165840 | Space Travel | Mars | ******* | the Moon | 22/01/2020 | 12:54:06 | Ready | Mickymouse 3 | |
165887 | Space Travel | Mars | ******* | the Moon | 22/01/2020 | 13:40:42 | Ready | Mickymouse 4 | |
165934 | Space Travel | Mars | ******* | the Moon | 22/01/2020 | 14:10:18 | Ready | Mickymouse 5 | |
165968 | Space Travel | Mars | ******* | the Moon | 22/01/2020 | 15:57:21 | Ready | Mickymouse 5 | |
165975 | Space Travel | Mars | ******* | the Moon | 22/01/2020 | 16:09:32 | Ready | Mickymouse 6 | |
165976 | Space Travel | Mars | ******* | the Moon | 22/01/2020 | 16:10:32 | Ready | Mickymouse 6 | |
165978 | Space Travel | Mars | ******* | the Moon | 22/01/2020 | 16:11:49 | Not Ready | Mickymouse 6 | |
166123 | Space Travel | Mars | ******* | the Moon | 23/01/2020 | 08:41:16 | Not Ready | Mickymouse 7 | |
166331 | Space Travel | Mars | ******* | the Moon | 23/01/2020 | 14:48:53 | Ready | Mickymouse 6 | |
166332 | Space Travel | Mars | ******* | the Moon | 23/01/2020 | 14:49:56 | Not Ready | Mickymouse 6 | |
166386 | Space Travel | Mars | ******* | the Moon | 23/01/2020 | 16:06:46 | Ready | Mickymouse 5 | |
166389 | Space Travel | Mars | ******* | the Moon | 23/01/2020 | 16:07:52 | Ready | Mickymouse 6 | |
166392 | Space Travel | Mars | ******* | the Moon | 23/01/2020 | 16:10:34 | Ready | Mickymouse 5 | |
166435 | Space Travel | Mars | ******* | the Moon | 23/01/2020 | 23:07:59 | Ready | Mickymouse 8 | |
166440 | Space Travel | Mars | ******* | the Moon | 24/01/2020 | 07:09:26 | Ready | Mickymouse 8 | |
166462 | Space Travel | Mars | ******* | the Moon | 24/01/2020 | 07:56:55 | Not Ready | Mickymouse 8 | |
166465 | Space Travel | Mars | ******* | the Moon | 24/01/2020 | 07:58:14 | Ready | Mickymouse 8 | |
166467 | Space Travel | Mars | ******* | the Moon | 24/01/2020 | 07:59:31 | Ready | Mickymouse 8 | |
166471 | Space Travel | Mars | ******* | the Moon | 24/01/2020 | 08:01:41 | Not Ready | Mickymouse 8 | |
166472 | Space Travel | Mars | ******* | the Moon | 24/01/2020 | 08:04:34 | Ready | Mickymouse 8 | |
