Spreadsheet: 863909d1711304857-index-aggregate-vlookup-across-tables-issue-order-entry-issues
Sheet: Order Entry

 | Order Details
 | Cust FDS Code: | CUST1 | Req'd Ship/PU: | 2099-02-21 00:00:00 | SRF#: | 12345 |  | COMPOSE & SEND FLAGGED EMAIL FOR REVIEW
 | Cust PO#: | 123456PO | Freight: | [calculated] |  |  |  | Send Flagged [NAME]
 |  | Item |  |  |  | Qty | Flag? | [NAME]:
 | Item 1 | 01] 12-3456 | Item 1 | 01234567891011 |  |  |  | 100 |  | Customer: | Customer 1 | Requested Ship:
 | Item 2 | 05] 12-3460 | Item 4 | 01234567891015 |  |  |  | 200 | X | SRF#: |  | Within Lead Time:
 | Item 3 | 10] 12-3465 | Item 9 | 01234567891020 |  |  |  | 300 |  | PO#: |  | Lead-Compliant Ship: | 2099-02-21 00:00:00
 | Item 4 | 03] 12-3458 | Item 3 | 01234567891013 |  |  |  | 400 | X | Flagged | Quantity | Not Flagged | Quantity
 | Item 5 | 06] 12-3461 | Item 5 | 01234567891016 |  |  |  | 500
 | Item 6
 | Item 7
 | Item 8
 | Item 9
 | Item 10
 | Item 11
 | Item 12
 | Item 13
 | Item 14
 | Item 15
I need this particular section of my worksheet to do several things.
1) Evaluate if C5:C19 has an item from the drop down list (created using Data Validation) selected
2) Evaluate if H5:H19 has any value in it at all to indicate the particular item flagged as unavailable in order entry
3) Have rows with data from the black cells appear in the correct column in the small table to the right: Item Number and Quantity in I:J for items that have flagged, and K:L for items that are okay
 | Here's the rub:
 | a) I don't want what is appearing in C5:C19 to appear in columns I/L.  These drop down lists are set up so that no matter what piece of info for an item someone starts to enter, it's going to find a match
 | if one exists. BUT, I only want the ACTUAL Item Number (Column 1 from the Products tab) to display in either Column I or Column K, depending on whether it flagged or not.
 | b) I need the items from the order area to appear in the correct order under their respective Flagged/Not Flagged columns, along with their corresponding quantities
 |  | i) I need to make sure the rows containing data in each section (Flagged/Not Flagged) are displayed "aggregated," not doing any math, but making sure blank rows are ignored when sorted, AND listed in the order they appear on the Products worksheet.
 |  | In the example above, the table to the right SHOULD be auto-populated to appear as follows:
 |  |  | Flagged | Quantity | Not Flagged | Quantity
 |  |  | 12-3458 | 400 | 12-3456 | 100
 |  |  | 12-3460 | 200 | 12-3465 | 300
 |  |  |  |  | 12-3461 | 500
 |  | This is because the flagged items and quantities are being sorted and aggregated independent of the Not Flagged items, shown in the order they appear on the Products list; same with the Not Flagged items, sorted and aggregated independent of the Flagged items.
 | So, in the above example, as I am selecting items from each row's dropdown list and entering quantities, I'm not entering the order in Order Processing at the same time - I am just entering a list of items from a customer's PO.
 | Once all items and quantities are entered, I also need that list to be sorted in the same order they appear in the Products tab, so I had to create a macro and assign a button to automate this process, which is what "[NAME]" does.
 |  | This isn't ideal, as I would have rather have had the list sort in place, but I don't think this is possible based on data layout/locations, which is fine - a workaround was found.
 |  | This is why each item has a 2-digit number followed by a closing bracket in front, so it can sort in place accordingly using the macro button. Clumsy, but effective.
 |  | The reason items have to be listed in a specific order (outlined on the Products tab) is because that is the order items need entered in Order Processing for eventual picking/fulfillment.
 | As I am entering items in the Order cells, info should automatically begin appearing under the "Not Flagged" section of the table to the right, since nothing has been entered in Order Processing yet, so we don't know if anything will flag yet
 |  | The actual Item Number, again from Column 1 of the Products tab, should be listed under Not Flagged, and the corresponding quantity
 | As the order is being entered in Order Processing, I get a notification of whether that item is "flagged", and I place an "X" in the Flagged column to indicate such, as I will have to get verification of whether or not I can accommodate the order as-requested.
 | Eventually I will set it up with conditional formatting to highlight specific cells of a row to indicate that item is flagged.  But this should ALSO move the product's actual item number and quantity OUT of the Not Flagged section of the table to the right, and move it under the Flagged section.
 | As items are marked as Flagged and others left unflagged, the table to the right should show every item from the order in its' respective column, in the correct order, with no blank rows in-between entries.
I actually had this working under another version of this worksheet, but it required me having the entire product list on each and every order worksheet (there are a total of 15; I'm only doing 1 in my sample file), I had to enter quantities for the ordered items, and it would aggregate both the order
section (the area filled in black above) and the flagged email section accordingly.  This was stupid and made each sheet needlessly long when I SHOULD be able to just have all product details on one worksheet and reference that data on each Order Sheet without having a copy of it on that physical sheet.
The base formula I used for this before was as follows:
 | =IFERROR(@INDEX($A$70:$I$198,AGGREGATE(15,6,(ROW($70:$198)-69)/($D$70:$D$198<>""),ROWS($1:$1)),1),"")
This was modified based on what cell it was in, what data I was trying to indicate, etc.  It worked well.  But now that I am trying to reference data from another table on another sheet and return actual product numbers based on a string of text from a drop down list, things have gotten...complicated.
Since I have to either do a VLOOKUP or an INDEX/MATCH function (or even both??) to return just the product number from the string of text in the drop down list, AND sort/aggregate the values, I'm out of my league here.