[Answered] Automated Attendance + Salary Sheet

[Nikhil Patki]

I've created Automated Attendance + Salary Workbook on office 365 version.
it compromises of 4 sheets in total
Sheet 1 - detailed info about employees in table format

Sheet 2 - Attendance sheet
filtered data of employees in table format including attendance log
advance filter macro is recorded and linked with a shape which helps to extract data every time the shape (button) is clicked.

Sheet 3 - Salary sheet
employee data linked with simple formula from sheet 2
formula used to link employee data from sheet is =Sheet2! B4
same formula is pasted on all corresponding cells
further there is countif formula to have each employee's present, absent and half days in respective cells.
later cells contains basic formula to calculate amount for absent and half days
further cells respectively have advance, fine, extra trip data which is manually entered
and last column of total Net pay calculating all the necessary fields

Sheet 4 - Salary Slip
Emp id has data validation of list
and rest fields has vlookup formula used to extract necessary header details from sheet 3 as per the emp id
and lastly basic formula to calculate and input addition / subtraction and Net pay amount.

now issue i use this file in office 2013 ver
and i face issue only when i try to input value manually in sheet 3 - fields advance / fine / extra trip
whenever value is entered excel screen freezes and a error message is popped
mentioning Excel has encountered an error, finding online solution

Solution is never found and excel gets restarted
or else I've to press cancel.
when pressed cancel excel closes and I've to re-open the file again.

 

[Seemanto Saha]

I've created Automated Attendance + Salary Workbook on office 365 version.
it compromises of 4 sheets in total
Sheet 1 - detailed info about employees in table format

Sheet 2 - Attendance sheet
filtered data of employees in table format including attendance log
advance filter macro is recorded and linked with a shape which helps to extract data every time the shape (button) is clicked.

Sheet 3 - Salary sheet
employee data linked with simple formula from sheet 2
formula used to link employee data from sheet is =Sheet2! B4
same formula is pasted on all corresponding cells
further there is countif formula to have each employee's present, absent and half days in respective cells.
later cells contains basic formula to calculate amount for absent and half days
further cells respectively have advance, fine, extra trip data which is manually entered
and last column of total Net pay calculating all the necessary fields

Sheet 4 - Salary Slip
Emp id has data validation of list
and rest fields has vlookup formula used to extract necessary header details from sheet 3 as per the emp id
and lastly basic formula to calculate and input addition / subtraction and Net pay amount.

now issue i use this file in office 2013 ver
and i face issue only when i try to input value manually in sheet 3 - fields advance / fine / extra trip
whenever value is entered excel screen freezes and a error message is popped
mentioning Excel has encountered an error, finding online solution

Solution is never found and excel gets restarted
or else I've to press cancel.
when pressed cancel excel closes and I've to re-open the file again.

Hello Nikhil Patki,

Thanks for sharing your problem with us. I understand that you are facing issues while working in the Excel 2013 version with an automated workbook that was created with the Office 365 version.
As you haven't mentioned the exact error message, it is not possible to specifically say where the error might be occurring. Understanding the words of the error message would provide valuable clues to the root cause.
However, as the entire Excel screen is freezing, there can be formula errors or compatibility issues. To fix these problems, you can try the following troubleshooting methods:​

• Compatibility Mode:
  Some of the features for formulas of the Office 365 version may not be available in Excel 2013. In such cases, open the workbook in Office 2013 using Compatibility Mode to ensure that older version functionality is prioritized. To learn about Compatibility Mode in Excel, you can follow the articles below:​
  1. Compatibility Mode in Excel
  2. How to Change Compatibility Mode in Excel
     
• Formula Evaluation:
  Select any cell with formula >> go to the Formulas tab >> click Evaluate Formula command.
  
  Afterward, you will get the Evaluate Formula dialog box. You can click the Evaluate button to see the result of each part of the formula. Look for circular references, errors, or complex calculations here that might be slowing things down.
  ​

I hope this will give you an idea of how to fix your problem. If the workbook shows further errors, please share a sample workbook and the particular error message.​

Regards,
Seemanto Saha
ExcelDemy

 

[Nikhil Patki]

I have ensured to use simple and basic formula as I work for such tasks at my residence using office 365. Whereas I have to finish my tasks in office on 2013 version.

Also the error message is exactly same as mentioned. There are no error code or any other extra description about error.