Birthday wish

[aratrika]

I am maintaining salary sheet and payslip in excel for all of my employee. Date of birth and marriage dates are maintained in a separate excel file. Now i want the birth day wishes to be printed on the payslip automatically in the respective month by picking up the data from the file where the date of birth is maintained.Can any body suggest any excel formula to achieve the same. look forward for the valuable input as always.


aratrika

 

[bfraser]

You could copy and paste a link to the dates into the salary and payslip workbook. Then use an if statement to show any birthday/anniversary message.

 

[aratrika]

Thanks but not understood how will it work, could you please elaborate a further.


aratrika

 

[vijaySharma]

Aratrika,


Try this...


on the Payslip sheet (assuming the Employee ID is in cell A1)


=vlookup(A1,OtherSheetForDOB!A1:B500,2,0)


Assuming the OtherSheetForDOB is the name of the sheet, and column A contains Employee ID and Columns B contains Birth Date).


If using Excel 2003 ... below is the error handler


=if(iserror(vlookup(A1,OtherSheetForDOB!A1:B500,2,0))," ",vlookup(A1,OtherSheetForDOB!A1:B500,2,0))


Excel 2007 and above


=iferror(vlookup(A1,OtherSheetForDOB!A1:B500,2,0),"")


~VijaySharma

 

[Atanu Chanda]

Aratrika,


You have to identify the common field names first,i.e., Emp name /EmpID between two worksheets.

Name ranges to the columns containing common field name in cell[A] & DOB cell from "Define names" say DOB_RANGE.

Now use vlookup to extract the values based on common field name.


=iferror(vlookup(common field name,Date of birth and marriage datesworksheet name!DOB_RANGE,2,0),"")


Atanu

 

[aratrika]

Atanu & VJ


Thanks for your input but it is still not understood and it seems I must elaborate a little more. I have a file (say file X.xls.) containing name of the employee with DOB. I have an another file (say Y.xls) where I used to calculate salary every month and accordingly payslips are generated in another file(say Z.xls)which is containing all the payslips of all zone/states in different sheets. Now I want the B'day wishes to be picked automatically in accordance with the DOB in the payslip file(file Z.xls.) I believe that I may have to create an another file consisting the message on different occasions and that can be named as Message.xls. Now I seek your suggestion on the task as mentioned. Thanking you in anticipation.

aratrika