I'm in an =IFERROR(INDEX pickle. Need to add additional rows for auto entry on 2nd worksheet

[Machead]

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!

 

[Aladin Akyurek]

Let's suppose that you enter this formula in A20 of the invoice sheet (adjust to suit).

A20, control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX('time sheet'!D$10:D$200,SMALL(IF((ISNUMBER('time sheet'!$D$10:$D$20),IF('time sheet'!$D$10:$D$20>0),ROW('time sheet'!$A$10:$A$200)-ROW('time sheet'!$A$10)+1),ROWS($A$20:A20))),"")

 

[Machead]

Let's suppose that you enter this formula in A20 of the invoice sheet (adjust to suit).

A20, control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX('time sheet'!D$10:D$200,SMALL(IF((ISNUMBER('time sheet'!$D$10:$D$20),IF('time sheet'!$D$10:$D$20>0),ROW('time sheet'!$A$10:$A$200)-ROW('time sheet'!$A$10)+1),ROWS($A$20:A20))),"")

Thank you for your time. Still scratching my head over hear. The formula I have on the Invoice page works... it just stops working after I reach a certain row.

I'm not sure if your proposal addresses the scenario. A20 is a reference point on the 'Time Sheet' (worksheet 1; i.e., the data I wish to have selectively copied over into worksheet 2). The location for where I wish to import the data on the Invoice (worksheet 2) doesn't start until A28, so i'm not sure why i would be plugging in formulas in any row beforehand.

I did enter the formula your wrote anyway on A20 of the invoice with control+ shift+ enter but it didn't work. 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?

 

[Aladin Akyurek]

Thank you for your time. Still scratching my head over hear. The formula I have on the Invoice page works... it just stops working after I reach a certain row.

I'm not sure if your proposal addresses the scenario. A20 is a reference point on the 'Time Sheet' (worksheet 1; i.e., the data I wish to have selectively copied over into worksheet 2). The location for where I wish to import the data on the Invoice (worksheet 2) doesn't start until A28, so i'm not sure why i would be plugging in formulas in any row beforehand.

I did enter the formula your wrote anyway on A20 of the invoice with control+ shift+ enter but it didn't work. 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?

Destination sheet...

A28, control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX('time sheet'!$D$10:$D$200,SMALL(IF((ISNUMBER('time sheet'!$D$10:$D$200),IF('time sheet'!$D$10:$D$200>0),ROW('time sheet'!$D$10:$D$200)-ROW('time sheet'!$D$10)+1),ROWS($A$28:A28))),"")

 

[Machead]

Destination sheet...

A28, control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX('time sheet'!$D$10:$D$200,SMALL(IF((ISNUMBER('time sheet'!$D$10:$D$200),IF('time sheet'!$D$10:$D$200>0),ROW('time sheet'!$D$10:$D$200)-ROW('time sheet'!$D$10)+1),ROWS($A$28:A28))),"")

Thank you for the follow up. I tried inputting the formula you gave me into destination cell A28. After I inputted the formula, i pressed and held down Control + Shift +Enter. I got an error message like before wherein Excel highlights the ">0" in the formula and gives me the message: IF(logical_test,[value_if_true],[value_if_false]). Derp.

Maybe control + shift+ enter doesn't work when using excel on a macbook?

 

[Aladin Akyurek]

Thank you for the follow up. I tried inputting the formula you gave me into destination cell A28. After I inputted the formula, i pressed and held down Control + Shift +Enter. I got an error message like before wherein Excel highlights the ">0" in the formula and gives me the message: IF(logical_test,[value_if_true],[value_if_false]). Derp.

Maybe control + shift+ enter doesn't work when using excel on a macbook?

Control+shif+enter == command+return (I think).

A28, control+shift+enter, not just enter, and copy down:

 =IFERROR(INDEX('time sheet'!$D$10:$D$200,
    SMALL(IF((ISNUMBER('time sheet'!$D$10:$D$200),
    IF('time sheet'!$D$10:$D$200>0,ROW('time sheet'!$D$10:$D$200)-ROW('time sheet'!$D$10)+1)),
    ROWS($A$28:A28))),"")