Solved

# Autofill Invoice Tasks in Excel using Index/Match

Posted on 2016-07-15
66 Views
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)),"")}
``````
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
0
Question by:TechNinja2
• 4
• 3

LVL 28

Accepted Solution

Subodh Tiwari (Neeraj) earned 500 total points
ID: 41712991
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))),"")
``````

For details, refer to the attached.
Invoices.xlsx
1

Author Comment

ID: 41717297
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

LVL 28

Expert Comment

ID: 41717300
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

Author Comment

ID: 41717421
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))),"")}
``````

Thx!
0

LVL 28

Expert Comment

ID: 41717463
Obviously tt's correct if you are getting the desired output. :)
0

Author Closing Comment

ID: 41732197
Occam's razor - I get it - I overthought it.  I wanted to ensure I didn't miss something.  Thx - Great job!
0

LVL 28

Expert Comment

ID: 41732381
Thanks for the feedback. :)
0

## Featured Post

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directiâ€¦
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabilâ€¦
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.