Hi Helpers,
I'm not sure if this is indeed possible, but thought i'd give it a go as you all have been so helpful in the past. I have two tables in two separate worksheets ("WKST1" and "INV2") as shown below. INV2 references WKST1. In Column D of INV2 I am using a SUMIF equation to total all Remaining material that share the same location, grade, and company from Column F of WKST 1. So, for example, I used the equation
=Sumif(WKST1!F:F,WKST1!D:D,"SteelCore",WKST1!A:A,'INV2'!A4,WKST1!B:B,'INV2'!B4)
in Cell D4 of INV2 to gather the total amount of lbs of material in New York/275F Grade/SteelCore Company.
I would like to know if there is any equation(s) I can use in Column E of INV2 that will gather the P.O. #(s) from WKST1 that contribute to the sum of each Location/Grade/Company combination in INV2? For instance, P.O. #'s 11111 and 22222 from WKST1 make up the 30 lbs that are remaining in New York/Grade 275F/SteelCore Company (Cell D4 of INV2).
I realize this is likely not possible as some of the combinations in INV2 will return multiple P.O. #'s (like in the instance noted above), but was wondering if any of you out there with greater EXCEL minds than my own might have any ideas on alternative ways I could go about doing this???
At first glance i thought it could be accomplished through the use of a Pivot Table, however, that did not solve the problem.
I'd greatly appreciate any advice or feedback any of you could offer.
WKST1:
<tbody>
</tbody>
<tbody>
</tbody>
INV2:
<tbody>
</tbody>
<tbody>
</tbody>
=Sumif(WKST1!F:F,WKST1!D:D,"SteelCore",WKST1!A:A,'INV2'!A4,WKST1!B:B,'INV2'!B4)**
Thanks!
I'm not sure if this is indeed possible, but thought i'd give it a go as you all have been so helpful in the past. I have two tables in two separate worksheets ("WKST1" and "INV2") as shown below. INV2 references WKST1. In Column D of INV2 I am using a SUMIF equation to total all Remaining material that share the same location, grade, and company from Column F of WKST 1. So, for example, I used the equation
=Sumif(WKST1!F:F,WKST1!D:D,"SteelCore",WKST1!A:A,'INV2'!A4,WKST1!B:B,'INV2'!B4)
in Cell D4 of INV2 to gather the total amount of lbs of material in New York/275F Grade/SteelCore Company.
I would like to know if there is any equation(s) I can use in Column E of INV2 that will gather the P.O. #(s) from WKST1 that contribute to the sum of each Location/Grade/Company combination in INV2? For instance, P.O. #'s 11111 and 22222 from WKST1 make up the 30 lbs that are remaining in New York/Grade 275F/SteelCore Company (Cell D4 of INV2).
I realize this is likely not possible as some of the combinations in INV2 will return multiple P.O. #'s (like in the instance noted above), but was wondering if any of you out there with greater EXCEL minds than my own might have any ideas on alternative ways I could go about doing this???
At first glance i thought it could be accomplished through the use of a Pivot Table, however, that did not solve the problem.
I'd greatly appreciate any advice or feedback any of you could offer.
WKST1:
<tbody>
</tbody>
Location | Grade | P.O.# | Company | Total P.O. (lbs) | Remaining on P.O. (lbs) |
New York | 275F | 11111 | SteelCore | 50 | 20 |
New York | 275F | 22222 | SteelCore | 30 | 10 |
New York | 180F | 99999 | ABC Inc | 60 | 25 |
New York | 275F | 44444 | ABC Inc | 10 | 5 |
Chicago | 180F | 55555 | SteelCore | 50 | 15 |
Chicago | 180F | 66666 | ABC Inc | 40 | 15 |
Chicago | 180F | 88888 | ABC Inc | 20 | 10 |
Chicago | 275F | 33333 | ABC Inc | 15 | 5 |
<tbody>
</tbody>
INV2:
Location | Grade | Company | Amt Grade Remaining (lbs) | P.O. #(s) |
New York | 180F | SteelCore | 0 | |
New York | 180F | ABC Inc | 25 | |
New York | 275F | SteelCore | 30** | |
New York | 275F | ABC Inc | 5 | |
Chicago | 180F | SteelCore | 15 | |
Chicago | 180F | ABC Inc | 25 | |
Chicago | 275F | SteelCore | 0 | |
Chicago | 275F | ABC Inc | 5 |
<tbody>
</tbody>
<tbody>
</tbody>
=Sumif(WKST1!F:F,WKST1!D:D,"SteelCore",WKST1!A:A,'INV2'!A4,WKST1!B:B,'INV2'!B4)**
Thanks!