Automatically fill in a positions' chart with employee details

Son

Active Member
Joined
Mar 19, 2010
Messages
281
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!
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Son

Active Member
Joined
Mar 19, 2010
Messages
281
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,112,772
Messages
5,542,445
Members
410,552
Latest member
Yogesh977
Top