Hi - I have been working on this for a while and cannot figure it out - your help is greatly appreciated!!
I have a tab with a list of employee codes and their hours worked.
I have a 2nd tab with a list of employee codes and their cost.
I have written the formula to multiply them together - simple enough.
Here is where the trouble starts:
I need to be able to have duplicate employee codes on the 1st tab, with the ability to add rows and new employees (more of the same codes). So I need to have multiple instances of the same employee code on the 1st tab pointing to the same cost on the 2nd tab.
Then I need to have the ability to filter based on another criteria, and have the sumproduct recalculate based on just the items filtered on, (like subtotal(109)) would do.
To accomplish all of this I have created
=SUMPRODUCT(SUBTOTAL(103,OFFSET(Y7:Y30,ROW(Y7:Y30)-MIN(ROW(Y7:Y30)),,1)),(($D$7:$D$30='Rate Tables'!$B$7:$B$30)*1),(Y7:Y30*'Rate Tables'!$D$7:$D$30))
The problem is that creating a new row within this range causes an #N/A. I removed the $ so that I was pulling the full columns, which allowed me to add rows, however it did not recalculate or take the 2nd instance of the employee code into account.
I unfortunately cannot share the file due to the confidential costs.
Any idea on how I can solve this???
I have a tab with a list of employee codes and their hours worked.
I have a 2nd tab with a list of employee codes and their cost.
I have written the formula to multiply them together - simple enough.
Here is where the trouble starts:
I need to be able to have duplicate employee codes on the 1st tab, with the ability to add rows and new employees (more of the same codes). So I need to have multiple instances of the same employee code on the 1st tab pointing to the same cost on the 2nd tab.
Then I need to have the ability to filter based on another criteria, and have the sumproduct recalculate based on just the items filtered on, (like subtotal(109)) would do.
To accomplish all of this I have created
=SUMPRODUCT(SUBTOTAL(103,OFFSET(Y7:Y30,ROW(Y7:Y30)-MIN(ROW(Y7:Y30)),,1)),(($D$7:$D$30='Rate Tables'!$B$7:$B$30)*1),(Y7:Y30*'Rate Tables'!$D$7:$D$30))
The problem is that creating a new row within this range causes an #N/A. I removed the $ so that I was pulling the full columns, which allowed me to add rows, however it did not recalculate or take the 2nd instance of the employee code into account.
I unfortunately cannot share the file due to the confidential costs.
Any idea on how I can solve this???
