I am struggling to find any way to insert a column into the MAIN tab pulling from the INV tab. The MAIN tab is one sales order for 1 customer with 29 rows, intentionally order qty of 1 in each. The INV Tab is the list of invoices for the same sales order. Some of the invoices were for quantities greater than 1, this is where my formulas do not work well. In using the SMALL fx, I can create a unique field for both tables and as long as the # of sale order rows = the # of invoice rows, I am good. Of course, it is not that simple.
My ex, see the highlight rows 9, 29, 30 & 31 in the Main tab. This shows that a qty of 4 total were listed in SO# 19424 SOLine# 4 & SORel# 1. Again, a line per qty of 1 is intentional to assist in the fx. In the INV Tab, the same lines are on rows 9, 10 & 11 (sorted by invoice date); however, row 11 on 12/22/2020 indicates qty 2 were invoiced. My dilemna shown on Main tab rows 30 & 31. I need a formula for col H for the invoice #.
Goal, the sales order is the MAIN data set with some "open" (not yet invoiced) lines and some "invoiced" lines. This spreadsheet will populate overnight and update the MAIN tab coming from the INV tab to indicate which lines have been invoices and which are still open. When qty's > 1 exist in the INV, I cannot figure a formula (fx) to populate Col H.
Note: I have a very large data set but only included one data set here. The most efficient formula (less time consuming in processing time) would be greatly appreciated but at this point, I will take whatever works. There will be many more columns added which I know those formulas making this file a large file when completed.
My last resort would be to find a fx that would force the INV tab to list at Qty 1 for each row, meaning repeat rows on the INV tab similar to the MAIN tab and then use a count/small function. But the level of effort to force both the INV and MAIN tabs to be singular (qty 1 each row) is also a resource ******.
Thank you very much.
My ex, see the highlight rows 9, 29, 30 & 31 in the Main tab. This shows that a qty of 4 total were listed in SO# 19424 SOLine# 4 & SORel# 1. Again, a line per qty of 1 is intentional to assist in the fx. In the INV Tab, the same lines are on rows 9, 10 & 11 (sorted by invoice date); however, row 11 on 12/22/2020 indicates qty 2 were invoiced. My dilemna shown on Main tab rows 30 & 31. I need a formula for col H for the invoice #.
Goal, the sales order is the MAIN data set with some "open" (not yet invoiced) lines and some "invoiced" lines. This spreadsheet will populate overnight and update the MAIN tab coming from the INV tab to indicate which lines have been invoices and which are still open. When qty's > 1 exist in the INV, I cannot figure a formula (fx) to populate Col H.
Note: I have a very large data set but only included one data set here. The most efficient formula (less time consuming in processing time) would be greatly appreciated but at this point, I will take whatever works. There will be many more columns added which I know those formulas making this file a large file when completed.
My last resort would be to find a fx that would force the INV tab to list at Qty 1 for each row, meaning repeat rows on the INV tab similar to the MAIN tab and then use a count/small function. But the level of effort to force both the INV and MAIN tabs to be singular (qty 1 each row) is also a resource ******.
Thank you very much.
MRExcel Help.xlsx | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | INVOICES | ||||||||
2 | 28.00 | $ 2,253,380.00 | |||||||
3 | SO# | SO Line | SO Rel | Inv Date | Inv# | Inv Qty | Invoice Total$ | ||
4 | 19424 | 0 | 0 | 12/2/2019 | 191090 | 0 | $ 676,014.00 | ||
5 | 19424 | 1 | 1 | 11/16/2020 | 201365 | 1 | $ 54,956.30 | ||
6 | 19424 | 1 | 1 | 11/19/2020 | 201374 | 1 | $ 54,956.30 | ||
7 | 19424 | 1 | 1 | 12/7/2020 | 201384 | 1 | $ 54,956.30 | ||
8 | 19424 | 1 | 1 | 12/7/2020 | 201382 | 1 | $ 54,956.30 | ||
9 | 19424 | 4 | 1 | 12/10/2020 | 201388 | 1 | $ 59,780.00 | ||
10 | 19424 | 4 | 1 | 12/11/2020 | 201390 | 1 | $ 59,780.00 | ||
11 | 19424 | 4 | 1 | 12/22/2020 | 201405 | 2 | $ 119,560.00 | ||
12 | 19424 | 5 | 1 | 12/22/2020 | 201406 | 1 | $ 59,780.00 | ||
13 | 19424 | 5 | 1 | 12/23/2020 | 201410 | 1 | $ 59,780.00 | ||
14 | 19424 | 5 | 1 | 1/12/2021 | 211416 | 1 | $ 59,780.00 | ||
15 | 19424 | 2 | 1 | 1/26/2021 | 211423 | 1 | $ 54,956.30 | ||
16 | 19424 | 2 | 1 | 1/27/2021 | 211424 | 1 | $ 54,956.30 | ||
17 | 19424 | 2 | 2 | 1/29/2021 | 211427 | 1 | $ 54,956.30 | ||
18 | 19424 | 2 | 2 | 2/8/2021 | 211428 | 1 | $ 54,956.30 | ||
19 | 19424 | 2 | 2 | 2/16/2021 | 211432 | 1 | $ 54,956.30 | ||
20 | 19424 | 2 | 2 | 2/22/2021 | 211439 | 1 | $ 54,956.30 | ||
21 | 19424 | 2 | 2 | 2/26/2021 | 211443 | 1 | $ 54,956.30 | ||
22 | 19424 | 2 | 2 | 3/8/2021 | 211446 | 1 | $ 54,956.30 | ||
23 | 19424 | 2 | 2 | 3/12/2021 | 211453 | 1 | $ 54,956.30 | ||
24 | 19424 | 2 | 2 | 3/22/2021 | 211460 | 1 | $ 54,956.30 | ||
25 | 19424 | 2 | 2 | 3/30/2021 | 211470 | 2 | $ 164,868.90 | ||
26 | 19424 | 3 | 1 | 3/30/2021 | 211470 | 1 | $ - | ||
27 | 19424 | 6 | 1 | 3/30/2021 | 211473 | 1 | $ 59,780.00 | ||
28 | 19424 | 3 | 1 | 4/6/2021 | 211482 | 1 | $ 54,956.30 | ||
29 | 19424 | 3 | 1 | 4/14/2021 | 211493 | 1 | $ 54,956.30 | ||
30 | 19424 | 3 | 1 | 5/27/2021 | 211547 | 1 | $ 54,956.30 | ||
31 | |||||||||
INV |
Cell Formulas | ||
---|---|---|
Range | Formula | |
F2:G2 | F2 | =+SUBTOTAL(9,F4:F30) |
MRExcel Help.xlsx | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | |||
3 | ORDER DATA | Need fx for this col | ||||||||||||
4 | Order Date | Order# | SOLine# | SORel# | ORDER QTY | Unit $ | Ext SO $ | Invoice# | ||||||
5 | 12/2/2019 | 19424 | 1 | 1 | 1 | $ 78,509.00 | $ 78,509.00 | |||||||
6 | 12/2/2019 | 19424 | 2 | 1 | 1 | $ 78,509.00 | $ 78,509.00 | |||||||
7 | 12/2/2019 | 19424 | 2 | 2 | 1 | $ 78,509.00 | $ 78,509.00 | |||||||
8 | 12/2/2019 | 19424 | 3 | 1 | 1 | $ 78,509.00 | $ 78,509.00 | |||||||
9 | 12/2/2019 | 19424 | 4 | 1 | 1 | $ 85,400.00 | $ 85,400.00 | 201388 | ||||||
10 | 12/2/2019 | 19424 | 5 | 1 | 1 | $ 85,400.00 | $ 85,400.00 | |||||||
11 | 12/2/2019 | 19424 | 6 | 1 | 1 | $ 85,400.00 | $ 85,400.00 | |||||||
12 | 12/2/2019 | 19424 | 7 | 1 | 0 | $ - | $ - | |||||||
13 | 12/2/2019 | 19424 | 1 | 1 | 1 | $ 78,509.00 | $ 78,509.00 | |||||||
14 | 12/2/2019 | 19424 | 1 | 1 | 1 | $ 78,509.00 | $ 78,509.00 | |||||||
15 | 12/2/2019 | 19424 | 1 | 1 | 1 | $ 78,509.00 | $ 78,509.00 | |||||||
16 | 12/2/2019 | 19424 | 2 | 1 | 1 | $ 78,509.00 | $ 78,509.00 | |||||||
17 | 12/2/2019 | 19424 | 2 | 2 | 1 | $ 78,509.00 | $ 78,509.00 | |||||||
18 | 12/2/2019 | 19424 | 2 | 2 | 1 | $ 78,509.00 | $ 78,509.00 | |||||||
19 | 12/2/2019 | 19424 | 2 | 2 | 1 | $ 78,509.00 | $ 78,509.00 | |||||||
20 | 12/2/2019 | 19424 | 2 | 2 | 1 | $ 78,509.00 | $ 78,509.00 | |||||||
21 | 12/2/2019 | 19424 | 2 | 2 | 1 | $ 78,509.00 | $ 78,509.00 | |||||||
22 | 12/2/2019 | 19424 | 2 | 2 | 1 | $ 78,509.00 | $ 78,509.00 | |||||||
23 | 12/2/2019 | 19424 | 2 | 2 | 1 | $ 78,509.00 | $ 78,509.00 | |||||||
24 | 12/2/2019 | 19424 | 2 | 2 | 1 | $ 78,509.00 | $ 78,509.00 | |||||||
25 | 12/2/2019 | 19424 | 2 | 2 | 1 | $ 78,509.00 | $ 78,509.00 | |||||||
26 | 12/2/2019 | 19424 | 3 | 1 | 1 | $ 78,509.00 | $ 78,509.00 | |||||||
27 | 12/2/2019 | 19424 | 3 | 1 | 1 | $ 78,509.00 | $ 78,509.00 | |||||||
28 | 12/2/2019 | 19424 | 3 | 1 | 1 | $ 78,509.00 | $ 78,509.00 | |||||||
29 | 12/2/2019 | 19424 | 4 | 1 | 1 | $ 85,400.00 | $ 85,400.00 | 201399 | ||||||
30 | 12/2/2019 | 19424 | 4 | 1 | 1 | $ 85,400.00 | $ 85,400.00 | 201405 | <=can't figure out how to report this | |||||
31 | 12/2/2019 | 19424 | 4 | 1 | 1 | $ 85,400.00 | $ 85,400.00 | 201405 | <=can't figure out how to report this | |||||
32 | 12/2/2019 | 19424 | 5 | 1 | 1 | $ 85,400.00 | $ 85,400.00 | |||||||
33 | 12/2/2019 | 19424 | 5 | 1 | 1 | $ 85,400.00 | $ 85,400.00 | |||||||
34 | ||||||||||||||
35 | ||||||||||||||
MAIN |
Cell Formulas | ||
---|---|---|
Range | Formula | |
G5:G33 | G5 | =+E5*F5 |