bestschnauzermom
New Member
- Joined
- Aug 9, 2019
- Messages
- 5
I'm building a fairly complicated spreadsheet that is going to be updated weekly. The spreadsheets name changes monthly so building a macro wouldn't be user friendly and I'm trying to build formulas with the workbook. The user imports data from another spreadsheet into "Week1". From there the data is pulled into the "Total" tab with the following formula.
Week 1 tab, column B is the employee, column E is their pay for the week and B8 is the employee name then multiplying by % of week.
=SUMIF(Week1!$B:$B,$B8,Week1!$E:$E)*$F6
After this information pulls to the Total tab I have another formula that splits the employee between two companies by department either 50% to 100% with this formula:
=IF(A11="K022","0%",IF(A11="K021","0%",IF(A11="K023","0%",IF(A11="C021/K021","50%",IF(A11="C022/K022","50%",IF(A11="C023/K023","50%","100%"))))))
=IF(D11="100%","0%",IF(D11="50%","50%",IF(D11="0%","100%")))
Then I have to take these and move them into the perspective companies / departments on three tabs. "Manager", "Clerical" or "Other".
[TABLE="width: 84"]
<colgroup><col></colgroup><tbody>[TR]
[TD]=(SUMIFS(Total!$F:$F,Total!$B:$B,$C8,Total!$C:$C,"Manager")
=SUMIFS(Total!F:F,Total!$B:$B,$B8,Total!$C:$C,"Clerical")
=(SUMIFS(Total!F:F,Total!$B:$B,$B6,Total!$C:$C,"Other")
[/TD]
[/TR]
</tbody>[/TABLE]
Here is the problem.
Data imports great
Data pulls to the total tab and breaks out percentages great
I cannot get the data WITH PERCENTAGES to pull to the department tabs.
I hope I explained this correctly, if not let me know.
Week 1 tab, column B is the employee, column E is their pay for the week and B8 is the employee name then multiplying by % of week.
=SUMIF(Week1!$B:$B,$B8,Week1!$E:$E)*$F6
After this information pulls to the Total tab I have another formula that splits the employee between two companies by department either 50% to 100% with this formula:
=IF(A11="K022","0%",IF(A11="K021","0%",IF(A11="K023","0%",IF(A11="C021/K021","50%",IF(A11="C022/K022","50%",IF(A11="C023/K023","50%","100%"))))))
=IF(D11="100%","0%",IF(D11="50%","50%",IF(D11="0%","100%")))
Then I have to take these and move them into the perspective companies / departments on three tabs. "Manager", "Clerical" or "Other".
[TABLE="width: 84"]
<colgroup><col></colgroup><tbody>[TR]
[TD]=(SUMIFS(Total!$F:$F,Total!$B:$B,$C8,Total!$C:$C,"Manager")
=SUMIFS(Total!F:F,Total!$B:$B,$B8,Total!$C:$C,"Clerical")
=(SUMIFS(Total!F:F,Total!$B:$B,$B6,Total!$C:$C,"Other")
[/TD]
[/TR]
</tbody>[/TABLE]
Here is the problem.
Data imports great
Data pulls to the total tab and breaks out percentages great
I cannot get the data WITH PERCENTAGES to pull to the department tabs.
I hope I explained this correctly, if not let me know.