Problem:
As HR are involved in issuing many certificates
and letters to banks and other institutions for their employees, such as
salary certificates, salary transfer letters...etc., which consumes a good portion of their time, they may get
into the hassle of using a regular template where they have to manually
enter employee information every time they are requested to issue such
letters.
Solution:
The task referred to above can be automated using the following steps,
assuming - as an example - that we want to automate a normal salary
certificate to whom it may concern. However, it should be noted that the
automation process assumes the existence of a full employee database on
MS Excel (or on a system that can export it to MS Excel), as the
automation will have to rely on such database to get employee
information.
The task is
tedious, however, once done, it should save you time and effort doing
salary certificates (or any other similar forms/templates) manually in the
future.
Note:
You may scroll to the end of the exercise to get the full formula,
however, it is recommended that you go through the steps as it is
complex.
Tools used: VLOOKUP, TEXT, TODAY, & (or alternatively CONCATENATE), and Data Validation.
1.
Ensure that you have all your employees information on MS Excel, such
as full name, joining date, position, gross salary, and net salary, to
name a few. We will use the following table in our example:
*
All names and other information in the shown table are imaginary and do
not intend to represent or refer to actual individuals. If any of the
names happens to be the name of a real person, it is pure coincidence.
2.
We will be using Data Validation to create a drop-down list where the
HR personnel would select the employee for whom the certificate is going
to be made.
Select
an empty cell where you wish to place the drop-down list, and let it be
B15 for our exercise purposes, then go to Data tab -->Data Tools
section --> Data Validation
You should see the following dialogue box:
Here
you should be able to restrict the data to be entered into the selected
cell. You can restrict data entry to certain data types (e.g. date,
time...etc.) and to certain limits (from / to). In our example, we will
restrict data entry to the list of employee names that are in the
employees database, and for that purpose, under Allow: select "List",
then under Source: select all cells that contain employees names, in our
example =A2:A11. Press OK. The selected cell should become a drop-down list where you can choose any employee name from the database.
3.
Now that you have created the drop-down list, we will work on the
design of the certificate. The intended certificate should look
something similar to the following:
4. Titles and company name/signature are all entered once (manually).
5. For the certificate date, enter the following formula to return the date of today:
=TODAY()
Straightforward.
However, note that this function is volatile, i.e. it gets updated
whenever you open this sheet to reflect the date of today (the day you
open it). If you want it fixed for archiving old certificates, you need
to copy the contents and paste as values. However, it should not be
needed as the certificate should supposedly be printed out and signed.
6.
Now comes the key step; automating the content of the certificate. The
sentence will be part as text (the text that does not change for all
salary certificates), and the other part is variable depending on the
selected employee name, such as the date of joining, basic salary...etc.
The following illustration highlights variable text, while the rest is
fixed:
In the formula, for each fixed text part, you will enter the exact text between quotations " ".
For the variable part, each one has a certain function to apply. We will start with them one by one.
To connect parts together, use the & symbol.
a. Employee name: Add a reference to the cell that contains the drop-down list. In our example, it is B15.
b.
Joining date: To get the joining date, use VLOOKUP based on the
selected employee name to look it up in the database and return the
date. The formula for that part should look like the following:
VLOOKUP(B15,A2:I11,2,FALSE)
If
you try to enter the formula referred to above in an empty cell, you
will get the number 40,448, which is the date (27/9/2010) based on the
number of days since 1/1/1900 as MS Excel treats dates. So what to do to
convert the date format to normal as illustrated above?
To
format the date (or any text) within a formula, use the TEXT function,
where you can format text the way you want. To use TEXT in this part,
the formula should become as follows:
TEXT(VLOOKUP(B15,A2:I11,2,FALSE),"d mmmm yyyy")
c. For Title, also use VLOOKUP as follows:
VLOOKUP(B15,A2:I11,3,FALSE)
d. For Basic Salary, use VLOOKUP and TEXT as follows:
TEXT(VLOOKUP(B15,A2:I11,6,FALSE),"#,###")
As
you can see, use hashtags to indicate the number formatting. You can
include/exclude the thousand comma separator as you wish.
Tip:
For more information on available options for text formatting, right
click on any cell and select Format Cells --> Number --> Custom.
e. For Gross Salary, use the same formula as in Basic Salary but change the column number as follows:
TEXT(VLOOKUP(B15,A2:I11,9,FALSE),"#,###")
7. Combining all of the above in one formula, the final formula should look like the following:
="This is to certify that "&B15&" has been employed with us since "&TEXT(VLOOKUP(B15,A2:I11,2,FALSE),"d mmmm yyyy")&" as a "&VLOOKUP(B15,A2:I11,3,FALSE)&" with a basic salary of "&"$"&TEXT(VLOOKUP(B15,A2:I11,6,FALSE),"#,###")&" and a gross salary of "&"$"&TEXT(VLOOKUP(B15,A2:I11,9,FALSE),"#,###")&"."
Voila! You have an automated salary certificate!
If you liked this exercise, please do not hesitate to share!
