Automatically fill in a positions' chart with employee details

Son

Active Member
Joined
Mar 19, 2010
Messages
284
Hi all! I have a question about how to automatically fill a form with employees.

In our company, the employees work in shifts that cover 24 hours a day.

It takes 5 persons (in shifts) to fill a post. One person works in the morning, one in the afternoon, one at night and two persons cover their absences. So, in total we need 5 persons for each post.

But we don't always have 5 persons in a post. Some times, only 4 or 3 people work in a post. In this case, the shifts are covered by persons working overtime.

Plus, there are employees that will retire by the end of this year, and employees that will retire next year.

I have a list of employees for each post, like this:

Post 1:
1. John, ID123, Retirement: 2020
2. Jim, ID234, Retirement: 2021
3. Mary, ID456, Retirement: 2025
etc

Also, I have the following chart, representing all the posts in the company and the way they are currently filled:

1602272076123.png


1602272237855.png


This chart show how many people we have currently working, how many will retire this year or next year, and how many people we need to recruit this year or next year.

What it does not show is which person is in which position in the company (ie which person is in each box)

So, my question is how can I automatically correlate each person to a position and update the chart.

I thought of assinging a number to each box (i.e position in the company) and then assign this number to each employee. Each employee has a unique ID number in our company. So, I could correlate the ID number to the Position number.

Ideally, I'd like to correlate the retirement year to the diagonal line I have in the boxes where people will retire this year or next year.

But then, when a person changes post and goes to another post, I would like to show this in the chart.

Also, I'd like to show the employee details in each box. Of course the boxes will need to be bigger for this.

As for the recruitment priority, I guess the director of each department will decide this. It would be risky to assign a priority code for each box. But I could assign a priority to each post. So, the 1st post would have first priority, the 2nd post would have second priority etc.

I am not sure I have explained all this very clearly, but I will be happy to answer any question you might have on this.

I'd really be grateful if you have any thoughts or ideas on how I could work on the chart.

Thanks in advance for any advice you might have for me!
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Hi all! I have a question about how to automatically fill a form with employees. In our company, the employees work in shifts that cover 24 hours a day. It takes 5 persons (in shifts) to fill a post. One person works in the morning, one in the afternoon, one at night and two persons cover their absences. So, in total we need 5 persons for each post. But we don't always have 5 persons in a post. Some times, only 4 or 3 people work in a post. In this case, the shifts are covered by persons working overtime. Plus, there are employees that will retire by the end of this year, and employees that will retire next year. I have a list of employees for each post, like this: Post 1: 1. John, ID123, Retirement: 2020 2. Jim, ID234, Retirement: 2021 3. Mary, ID456, Retirement: 2025 etc Also, I have the following chart, representing all the posts in the company and the way they are currently filled: View attachment 23934 View attachment 23935 This chart show how many people we have currently working, how many will retire this year or next year, and how many people we need to recruit this year or next year. What it does not show is which person is in which position in the company (ie which person is in each box) So, my question is how can I automatically correlate each person to a position and update the chart. I thought of assinging a number to each box (i.e position in the company) and then assign this number to each employee. Each employee has a unique ID number in our company. So, I could correlate the ID number to the Position number. Ideally, I'd like to correlate the retirement year to the diagonal line I have in the boxes where people will retire this year or next year. But then, when a person changes post and goes to another post, I would like to show this in the chart. Also, I'd like to show the employee details in each box. Of course the boxes will need to be bigger for this. As for the recruitment priority, I guess the director of each department will decide this. It would be risky to assign a priority code for each box. But I could assign a priority to each post. So, the 1st post would have first priority, the 2nd post would have second priority etc. I am not sure I have explained all this very clearly, but I will be happy to answer any question you might have on this. I'd really be grateful if you have any thoughts or ideas on how I could work on the chart. Thanks in advance for any advice you might have for me!
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,995
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top