Corporate Mobilization Forecast - Questions on improving, formulas & functions Excel

Hi all,


Little bit of background: I work in corporate company's HR department, and to be more specific in 'Mobilization Team' which I look after on-boarding process of nationals & expatriate employees. I have this mobilization spreadsheet to keep track of new starters and already mobilized personnel to company. I use this spreadsheet to report our progress.


Main point: The spreadsheet is completely 100% manual where I have to edit every single details to make it look right and it's not a nice thing to do. I want to improve this to keep better track of everything, up-to-date record etc.


What I want to improve:


1. Editing/modifying - when I change date, or input a data, it reflects the change to that whole row for that person to.
2. Adding formula/function - I can only see IF function. How do I add IFS for multiple conditions and values?
3. Multiple choice drop down list - If the personnel has completed first stage and currently stuck at second stage I want the STATUS to show : Medical Stage (First stage being : Contract Signing Stage; Third stage : SAP ID activation; Fourth stage : Mobilized). This is tied to 1 & 2 like when the person has signed the contract : IF(F7>=DATE(2016,9,13),'Mobilised'),F7 4. If the later stages are not confirmed, without given date and the cell is empty, the STATUS cell counts whatever cell has date or data in it. Example: If the Medical completed date has date and SAP number provided date is empty ; the status shows: Medical Stage
5. I want the STATUS cell to show 4 different colors in gradient bar. If it's stage 1: yellow ; stage 2: orange; stage 3; blue or something ; stage 4; green


Please help me as much as you can. English is not my native and first language - apologies if it doesn't make sense at all. But can you please edit and modify my attached (somehow I cant attach file) spreadsheet as close as possible to things I mentioned above?

Your assistance is greatly appreciated.

Mobilsation Spreadsheet Template.xlsx

First of all, your English is very good. Secondly, as HR spreadsheets go, this is not a bad one. These are all one-time requirements, so it is reasonable to use Excel to track them.
[code]
1. Editing/modifying - when I change date, or input a data, it reflects the change to that whole row for that person to.
[/quote]
I am not sure what you want here. Do you want to show the last time you updated anything in the row?

[quote]

2. Adding formula/function - I can only see IF function. How do I add IFS for multiple conditions and values?

There are a number of alternatives to IF. LOOKUP and VLOOKUP are very effective if you have a lot of values. See this article: http://www.utteraccess.com/wiki/IF_AND_OR_NOT

3. Multiple choice drop down list - If the personnel has completed first stage and currently stuck at second stage I want the STATUS to show : Medical Stage (First stage being : Contract Signing Stage; Third stage : SAP ID activation; Fourth stage : Mobilized). This is tied to 1 & 2 like when the person has signed the contract : IF(F7>=DATE(2016,9,13),'Mobilised'),F7 4. If the later stages are not confirmed, without given date and the cell is empty, the STATUS cell counts whatever cell has date or data in it. Example: If the Medical completed date has date and SAP number provided date is empty ; the status shows: Medical Stage

I think I see what you are looking for here. You are looking for the last field under one of these statuses that has a date in it.

5. I want the STATUS cell to show 4 different colors in gradient bar. If it's stage 1: yellow ; stage 2: orange; stage 3; blue or something ; stage 4; green

This can be accomplished using Conditional Formatting.

There are a couple of other things you can do with this spreadsheet.
- Convert the data into an Excel table. Excel tables automatically copy down formulas, formats, etc.
- Use data validations to provide drop-down lists for some of the cells or limit them to date-only entries.

Before I go too much further. Could you walk through a scenario: the person comes on board and then as you fill in each piece of data for the person, what do you want to happen?

I made a couple of changes to the form. First I converted it to an Excel Table. There are several advantages to Excel tables.
- You can use column headers in the formulas. This makes the formulas easier to read and understand.
- Formulas, formats and validations are copied down automatically.

I added some data validation. You now have dropdown lists for Position, Manager and Employment type. The values for these are in Excel tables on the Lookup sheet. If you add new values to these tables, they will be reflected in the dropdown lists.

I have two articles at the end of this post. One explains tables, the other explains data validations so you can maintain or create new validations. One shortcut that is not documented in the data validation article is that you can use a table column to validate data.

Create a list type validation and use something like this for the source: =INDIRECT(“Table_Name[Table Column]”).

The “working” formula is: =IF(ISNUMBER([@[SAP Received Date]]),"Mobilized",IF(ISNUMBER([@[SAP Number Submitted]]),"SAP Submitted",IF(ISNUMBER([@[Medical Received]]),"Medical Received",IF(ISNUMBER([@[Medical Completed]]),"Medical Completed",IF(ISNUMBER([@[Employment Contract Signed]]),"Contract Signed","")))))

It looks complicated, but it isn’t. What it does is check to see if there is a date in the SAP Received Date column, if so, the status is “Mobilized.” Then it checks for a date in the SAP Number Submitted Column. If so then the status is “SAP Sumbitted” and so on down the line.

The yellow shaded columns indicate that these are the milestones that determine a status.

So to use the form simply enter the employee’s name in the next available row. This makes the row part of the table, and the validations and formulas are copied down. Then as you key in dates under the milestone headers, the status will change. Skip over the status field when entering data. You can simply tab through it.

If you have a lot of real data, I suggest you do the following: copy and paste it immediately below the data here and then go back and delete the rows with test data.

http://www.utteraccess.com/wiki/Tables_in_Excel
http://www.utteraccess.com/wiki/Data_Validation

I think I got it. Instead of just checking for a SAP return date, I also check for a start date earlier or equal to the current day.

=IF(AND(ISNUMBER([@[SAP Received Date]]),[@[Travel to Site/First day in UB Office]]<=TODAY()),"Mobilized",IF(ISNUMBER([@[SAP Number Submitted]]),"SAP Submitted",IF(ISNUMBER([@[Medical Received]]),"Medical Received",IF(ISNUMBER([@[Medical Completed]]),"Medical Completed",IF(ISNUMBER([@[Employment Contract Signed]]),"Contract Signed","")))))

Coming in cold here, where is this formula?

You changed the name of one of the column headers. It is OK do to this after you have the formula in place. You apparently changed the name and then copied in the formula.

Change =IF(AND(ISNUMBER([@[SAP Received Date]]),[@[Travel to Site/First day in UB Office]]<=TODAY()),"Mobilized",IF(ISNUMBER([@[SAP Number Submitted]]),"SAP Submitted",IF(ISNUMBER([@[Medical Received]]),"Medical Received",IF(ISNUMBER([@[Medical Completed]]),"Medical Completed",IF(ISNUMBER([@[Employment Contract Signed]]),"Contract Signed","")))))

To =IF(AND(ISNUMBER([@[SAP Received Date]]),[@[Travel to Site/First day in UB Office]]<=TODAY()),"Mobilized",IF(ISNUMBER([@[SAP Submitted Date]]),"SAP Submitted",IF(ISNUMBER([@[Medical received]]),"Medical Received",IF(ISNUMBER([@[Medical Completed]]),"Medical Completed",IF(ISNUMBER([@[Employment Contract Signed]]),"Contract Signed","")))))

That would be with conditional formatting. You want the "Where Cell Contains" type of formatting. You will need one rule for each status. Here is some guidance on conditional formatting. In fact the example closely matches what you want to do.

This is getting to be a very complicated IF statement. The order in which things are evaluated is important. Where does SAP Number Provided fit. Most importantly what can happen after it that will change the status?

Also the IF statement does not address prerequisites. In other words, it is possible to Have an SAP number provided without ever submitting the SAP.

I am thinking that we need to replace the IF statement with VBA code that reads a table. In this table, you outline the dates that need to be entered in order. The VB program will read the table and decide if all steps that lead up to a milestone have been completed and provide a status if all steps have been completed or provide the status with an asterisk if the date exists but a previous step is missing. We can do some checking to see if a status has an asterisk and in addition to the color of the cell, add a pattern with conditional formatting.

Are you willing to make this a macro-enabled workbook to do this?

Here is the latest. I've done a spot check and it seems to work.

There is a new tab called Status. Fill in the Milestone - this must match one of the column headers. Also the order of the milestones is important. The status is associated with the last one of the milestones to have a date.

Fill in the status associated with the milestone. The Col Num should compute automatically. If it is #N/A then check the spelling for the milestone.

Cell F1 contains the default status.

The function itself is called GetStatus and it is called with one argument: the row number. So =GetStatus (Row()). The actual formula on the sheet is =IF([@POSITION]<>"",GetStatus(ROW()),""). The function is "hard coded" to read the Forecast sheet.

You can hide the Status sheet.

The Status Table makes the status flexible. If you add additional milestones the function will keep up.