This is the next steps of some much appreciated help I got from JeteMC and Czeslaw in a previous post. I'm closer than ever to getting this figured out (I think) but I'll try explain my challenges as simply as possible.
This is a custom project building / invoicing workbook with two tabs.
On the "Buildout" tab, I want to build a job using specific line items in a table with information relevant to me. (You'll see that part is built already.)
On the "Invoice1" tab, I want the ability to create an invoice based on which bill line items were billed on. This part is progressing nicely, but I'm stuck.
If you look at the attached file, you can see that the info that populates the table on the "Buildout" tab is pulled into the "Invoice1" tab using an Array formula... Like this...
=IFERROR(INDEX(Table1[Date],SMALL(IFERROR(ROW(A$1:A$100)/(Table1[Inv]=D$5),FALSE),ROW(A1))),"")
Here are some problems I need help solving...
1. When I use the pull-down menu in the Invoice1 Tab at Cell D5, I expect it to pull in all line items from the selected criteria, but it only pulls in one of many lines that match. You'll see this result when you change it from 1 to 2 and back again. What did I miss? [SOLVED] - uploaded corrected file - thank you mike7952
2. The Table in the "Buildout" tab has columns that allow user to enter billing increments. (For example, if a line item was $1,000 but was going to be broken into two billing invoices, user could enter $500 in Bill1 and $500 in Bill2 to break them up and keep them organized by date of billing. I want to make it so when the line items populate in the "Invoice1" tab after selecting the desired criteria in D5, the amount billed for that criteria shows up.) Like this... User selects "1" in "Invoice1" Cell D5. All the line items from the table in "Buildout" that correspond to the value selected in D5 list on the Invoice1 tab populate. Under the "Amount Due" column, the formula retrieves the dollar amount that matches the Bill1, Bill2, etc...)
In the attached example, you can see that my current capabilities are unable to 1. get ALL the line items related to the D5 selection to list, and 2. "Amount Due" is currently just pulling from the column "ActualVE" where I need it to find the dollar amount under Bill1 or Bill2 etc based on the D5 selection.
Simple but complex? I hope I did the problem some justice trying to explain it. The attached sheet should bring some clarity.
Thanks in advance...
Pete
Bookmarks