Hello. I'm new to Excel. More of an SPSS girl
I reached out to another excel forum and someone greatly helped me. But now that i'm having to input my actual (identifiable information) onto the worksheets, the formula provided is failing me. I believe it is because I'm having to add additional rows of data and am not accurately accounting for this in the formula. Would appreciate your help.
The formula they gave me meets my need of automating data entry from one worksheet (tracking employee work hours for several projects) into another worksheet (that serves as an invoice, broken down by project IDs). If an employee worked any hours on a project (project hrs cell>0), then I need their name, role and pay rate to appear on the invoice page along with the number of hours worked. If not, then I don't want their name to appear on the invoice under that particular project, even with a 0 hrs listed by their name.
The formula they plugged in basically looks like this:
=IFERROR(INDEX('time sheet'!D$10:D$20,SMALL(IF((ISNUMBER('time sheet'!$D$10:$D$20))*('time sheet'!$D$10:$D$20>0),ROW('time sheet'!$A$10:$A$20)-9),ROWS($1:1))),"")
and the initial reference points change as you move across the table of the invoice.
Problem is: my list of employees whose time I'm tracking is longer than the list I included when first receiving excel help. So the formula doesn't automatically transfer over any hours and employee info if the employee info is listed after row 20 on the time tracking worksheet. My list doesn't end at row 20 but instead row 30. So I tried to substitute 30 for 20, but it doesn't work. What am I missing?
Not sure if this will work but here is a link to the workbook in progress:
Example.xlsx
I really appreciate any insights. Thank you!
I reached out to another excel forum and someone greatly helped me. But now that i'm having to input my actual (identifiable information) onto the worksheets, the formula provided is failing me. I believe it is because I'm having to add additional rows of data and am not accurately accounting for this in the formula. Would appreciate your help.The formula they gave me meets my need of automating data entry from one worksheet (tracking employee work hours for several projects) into another worksheet (that serves as an invoice, broken down by project IDs). If an employee worked any hours on a project (project hrs cell>0), then I need their name, role and pay rate to appear on the invoice page along with the number of hours worked. If not, then I don't want their name to appear on the invoice under that particular project, even with a 0 hrs listed by their name.
The formula they plugged in basically looks like this:
=IFERROR(INDEX('time sheet'!D$10:D$20,SMALL(IF((ISNUMBER('time sheet'!$D$10:$D$20))*('time sheet'!$D$10:$D$20>0),ROW('time sheet'!$A$10:$A$20)-9),ROWS($1:1))),"")
and the initial reference points change as you move across the table of the invoice.
Problem is: my list of employees whose time I'm tracking is longer than the list I included when first receiving excel help. So the formula doesn't automatically transfer over any hours and employee info if the employee info is listed after row 20 on the time tracking worksheet. My list doesn't end at row 20 but instead row 30. So I tried to substitute 30 for 20, but it doesn't work. What am I missing?
Not sure if this will work but here is a link to the workbook in progress:
Example.xlsx
I really appreciate any insights. Thank you!
I also noticed that you put "20" in bold twice at the end but did other changes to the formula (not emboldened), like turning $A$20 into $A$200. Was that intentional?
