Ok so I have made a self populating Data Table to show me which employee is on what shift that updates daily from our roster.
This works and it's brilliant, however I want to steam line my finished product further, but haven't been able to find an easy solution.
I have several employees per position and my data table currently self populates so that every employee has their own line. I want to populate it so that all employees with the same position all apear in 1 row across the columns under their current shift.
So this is what my end result currently looks like
Table 1
<tbody>
</tbody>
This is how I want it to look.
Table 2
<tbody>
</tbody>
We have upto 250 employees and upto 50 positions so you can understand why I need to stream line the table.
I can not change the roster as it is our orginal data as requires to be set up as it is. When adding a new employee into the roster I must enter them into there designated crew. "inserting a row midway down the original data".
If I use the CONCATENATE formula and add a new employee into the roster "half way down the original data" then it throws out what the CONCATENATE formula is looking up. It looks up the same cell but the names have all moved down by a cell therefore the populated information is wrong.
I am hoping to make my end result look like Table 2 even if it means adding another spread sheet in somewhere between the roster and the end product.
Realisticly I want a Pivot table that produces names not a tally of employees per shift.
Any and all help on this problem will be most appreciated.
This works and it's brilliant, however I want to steam line my finished product further, but haven't been able to find an easy solution.
I have several employees per position and my data table currently self populates so that every employee has their own line. I want to populate it so that all employees with the same position all apear in 1 row across the columns under their current shift.
So this is what my end result currently looks like
Table 1
POSITION | SHIFT 1 | SHIFT 2 | SHIFT 3 |
POSITION 1 | EMPLOYEE 1 | ||
POSITION 1 | EMPLOYEE 5 | ||
POSITION 1 | EMPLOYEE 8 | ||
POSITON 2 | EMPLOYEE 4 | ||
POSITION 2 | EMPLOYEE 12 | ||
POSITION 2 | EMPLOYEE 20 |
<tbody>
</tbody>
This is how I want it to look.
Table 2
POSITION | SHIFT 1 | SHIFT 2 | SHIFT 3 |
POSITION 1 | EMPLOYEE 1 | EMPLOYEE 5 | EMPLOYEE 8 |
POSITION 2 | EMPLOYEE 4 | EMPLOYEE 12 | EMPLOYEE 20 |
POSITION 3 | EMPLOYEE 56 | EMPLOYEE | EMPLOYEE 9 |
POSITION 4 | EMPLOYEE 10 | EMPLOYEE 11 | EMPLOYEE 12 |
POSITION 5 | EMPLOYEE 13 | EMPLOYEE 14 | EMPLOYEE 15 |
POSITION 6 | EMPLOYEE 16 | EMPLOYEE 17 | EMPLOYEE 18 |
<tbody>
</tbody>
We have upto 250 employees and upto 50 positions so you can understand why I need to stream line the table.
I can not change the roster as it is our orginal data as requires to be set up as it is. When adding a new employee into the roster I must enter them into there designated crew. "inserting a row midway down the original data".
If I use the CONCATENATE formula and add a new employee into the roster "half way down the original data" then it throws out what the CONCATENATE formula is looking up. It looks up the same cell but the names have all moved down by a cell therefore the populated information is wrong.
I am hoping to make my end result look like Table 2 even if it means adding another spread sheet in somewhere between the roster and the end product.
Realisticly I want a Pivot table that produces names not a tally of employees per shift.
Any and all help on this problem will be most appreciated.