Autofill Invoice Tasks in Excel using Index/Match

Hello Experts!

I'm so close to a solution.  It is probably an easy fix - I've just hit coding writer's block.  

I created a spreadsheet that when the user changes the invoice number, it automagically populates the list of tasks used in that invoice period with this array formula:
{=IFERROR(INDEX(Task!$B$6:$B$11,SMALL(IF(LEN(Task!$D$6:$D$11)>0,ROW(Task!$A$6:$A$11)-ROW(Task!$A$5)),ROW(Task!1:1)),MATCH($C$3,Task!$C$1:$H$1,0)),"")}

Open in new window

It works great on Invoice 1, but when I switch to Invoice 2, it doesn't work.  I know why.  This part of the code (Task!$D$6:$D$11) needs to vary when I change from Invoice 1 to 2 or 3.  I could do a switch formula, but I'm hoping there is a more elegant solution.  Maybe nesting another match formula?  Here's where I need your help!

I have attached the file to help you visualize what I'm doing.  Any help is appreciated!
Invoices.xlsx
TechNinja2Asked:
Who is Participating?
 
Subodh Tiwari (Neeraj)Connect With a Mentor Excel & VBA ExpertCommented:
Try this....
=IFERROR(INDEX(Task!$B$6:$B$11,SMALL(IF(LEN(INDEX(Task!$C$6:$H$11,,MATCH("INVOICE "&$C$3,Task!$C$2:$H$2,0)+1))>0,ROW(Task!$A$6:$A$11)-ROW(Task!$A$5)),ROW(Task!1:1))),"")

Open in new window


For details, refer to the attached.
Invoices.xlsx
1
 
TechNinja2Author Commented:
Perfect - thank you Subodh!  

I have one more question, I'd like to total cost row.  I know the formula will be similar but it will be somehow matching the invoice number and task number in the Invoice worksheet to get the total cost amounts in ROW 'I' in the Task worksheet (see attached).  Can you help out?
Invoices.xlsx
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Mock up the desired output on the Invoice sheet and provide a mapping to the Task sheet as well to know what output you are trying to achieve and based on what conditions.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
TechNinja2Author Commented:
Ok - I think I figured it out.  Here's what I changed (and put it in cell E6 of the first tab/worksheet).  It seems to be pulling the correct totals.  Is this correct?

{=IFERROR(INDEX(Task!$I$6:$I$11,SMALL(IF(LEN(INDEX(Task!$C$6:$H$11,,MATCH("INVOICE "&$C$3,Task!$C$2:$H$2,0)+1))>0,ROW(Task!$A$6:$A$11)-ROW(Task!$A$5)),ROW(Task!1:1))),"")}

Open in new window


Thx!
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Obviously tt's correct if you are getting the desired output. :)
0
 
TechNinja2Author Commented:
Occam's razor - I get it - I overthought it.  I wanted to ensure I didn't miss something.  Thx - Great job!
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Thanks for the feedback. :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.