This is probably quite easy for an Excel expert, I hope!
I am carrying out a review on a team of engineering staff and the qualifications budgeted against each position within the firm. I have about 100 positions and staff and each position can hold a number of (in various combinations) qualifications. Within these positions, there are about 30 different roles within the firm, and a differing number of staff hold these roles. A staff member only holds one role. The team is split into a Level and Sub-level (trade) structure (ranking and skill structure if you like).
The review is to ascertain qualification shortfalls in each position and staff member, and decide what qualifications are required for each position.
Therefore I have a simple table, as follows.
Column A (A3-A100) is a list of all the positions.
Column B (B3-B100) is a list of roles associated to that position
Column C (C3-C100) in the surname of the person in that position
Row 1 (D1-B120) is a list of all the qualifications in alternate columns, meaning there are two columns per qualification, one for what the position requires, and two, what the present incumbent holds. (I hope this makes sense).
I use a simple convention for completing the grid.
For the position details,
Y = Qualification is Required, and is currently attached to that position. (short for Yes)
R = Qualification is required but not attached to that position yet (short for Required)
N = Qualification is curently attached to that position, but not required anymore for that position (short for Not Required)
Blank = not required.
For the present incumbent details,
H = Incumbant holds this qualification (short for Held)
S = Used where the incumbent requires this qualification (there will be a Y or R in the preceding column), but does not hold it (short for Shortfall).
The first analysis is to ascertain the changes to the qualifications attached to each position. This has to be in a particular format on another worksheet, position in Column A, and Qualifications attached to each position in Column B. There are a varying number of qualifications per position, so the output needs to be dynamic as I do not want gaps as follows: (this will in effect be a form I will submit to the budget holders stating the changes I need to occur).
Column A: Column B
Position Qualification
Position1: Qualification1
Qualification2
Qualification3
Position2: Qualification1
Qualification4
Position3: Qualification1
Qualification6
etc!
In addition, the qualification column needs formatting in a certain manner:
Qualification already attached to that position and requiring no change - black normal font.
Qualification to be attached to that position = Blue Bold (from the "R" in my naming convention above)
Qualification not required any more = Red Bold Font (from the "N) in my naming convention above)
I suppose what the incumbant actually holds or not is rather a red herring - the data and budget is about the position and the qualifications attached to that position. It will produce a training burden output when a person is assigned to that position.
The second analysis I need to produce is what shortfalls each incumbent has, and therefore producing that training burden to get him qualified to carry out that role.
I appreciate this is a long post and probably have given too much nugatory information, but hopefully I have got everything across in a straightforward manner.
Many thanks in advance for your help!
I am carrying out a review on a team of engineering staff and the qualifications budgeted against each position within the firm. I have about 100 positions and staff and each position can hold a number of (in various combinations) qualifications. Within these positions, there are about 30 different roles within the firm, and a differing number of staff hold these roles. A staff member only holds one role. The team is split into a Level and Sub-level (trade) structure (ranking and skill structure if you like).
The review is to ascertain qualification shortfalls in each position and staff member, and decide what qualifications are required for each position.
Therefore I have a simple table, as follows.
Column A (A3-A100) is a list of all the positions.
Column B (B3-B100) is a list of roles associated to that position
Column C (C3-C100) in the surname of the person in that position
Row 1 (D1-B120) is a list of all the qualifications in alternate columns, meaning there are two columns per qualification, one for what the position requires, and two, what the present incumbent holds. (I hope this makes sense).
I use a simple convention for completing the grid.
For the position details,
Y = Qualification is Required, and is currently attached to that position. (short for Yes)
R = Qualification is required but not attached to that position yet (short for Required)
N = Qualification is curently attached to that position, but not required anymore for that position (short for Not Required)
Blank = not required.
For the present incumbent details,
H = Incumbant holds this qualification (short for Held)
S = Used where the incumbent requires this qualification (there will be a Y or R in the preceding column), but does not hold it (short for Shortfall).
The first analysis is to ascertain the changes to the qualifications attached to each position. This has to be in a particular format on another worksheet, position in Column A, and Qualifications attached to each position in Column B. There are a varying number of qualifications per position, so the output needs to be dynamic as I do not want gaps as follows: (this will in effect be a form I will submit to the budget holders stating the changes I need to occur).
Column A: Column B
Position Qualification
Position1: Qualification1
Qualification2
Qualification3
Position2: Qualification1
Qualification4
Position3: Qualification1
Qualification6
etc!
In addition, the qualification column needs formatting in a certain manner:
Qualification already attached to that position and requiring no change - black normal font.
Qualification to be attached to that position = Blue Bold (from the "R" in my naming convention above)
Qualification not required any more = Red Bold Font (from the "N) in my naming convention above)
I suppose what the incumbant actually holds or not is rather a red herring - the data and budget is about the position and the qualifications attached to that position. It will produce a training burden output when a person is assigned to that position.
The second analysis I need to produce is what shortfalls each incumbent has, and therefore producing that training burden to get him qualified to carry out that role.
I appreciate this is a long post and probably have given too much nugatory information, but hopefully I have got everything across in a straightforward manner.
Many thanks in advance for your help!
