mjones
Board Regular
- Joined
- Oct 27, 2007
- Messages
- 94
- Office Version
- 365
- Platform
- Windows
Hi All,
Some sample data:
<tbody>
</tbody>
First time I tried to all a table so kindly forgive me if it doesn't work.
The columns for products A, B and C have quantities in the parts rows of how many of each part is needed to build the product.
What I hope you can help me with, is indicate (any way possible), that I should give Product B my attention. This is because I only need to order one part (Part 3) to build it. I don't want to know about Product C because I need to order two parts to build it (3 and 6).
In short, indicate the product columns that are short only one part (rows). It would be handy if one was in a cell so I could change it to a two when I'm looking for products needing two parts ordered.
The formula in the top row will gave quantity 1 above product A. It tells me there's enough stock to build one of product A, but product B in the next columns has enough parts in stock to build negative one products and I should order some parts. Product C can build negative two products.
I sure hope this explanation is clear. This is a very complicated spreadsheet and I left a lot out and I'm hoping it's enough.
Thanks a bunch!
Michele
Some sample data:
Can Make Qty > | {=IF(MAX(C$4:C$10)=0,"Unltd",INT(MIN(IFERROR($B$4:$B$10/C$4:C$10,1E+300))))} | {=IF(MAX(D$4:D$10)=0,"Unltd",INT(MIN(IFERROR($B$4:$B$10/D$4:D$10,1E+300))))} | {=IF(MAX(E$4:E$10)=0,"Unltd",INT(MIN(IFERROR($B$4:$B$10/E$4:E$10,1E+300))))} | |
Products > | A | B | C | |
Parts | In Stock | |||
1 | 15 | 5 | ||
2 | 22 | 4 | 11 | 11 |
3 | -4 | 4 | 2 | |
4 | 8 | 5 | 3 | 3 |
5 | 12 | 10 | ||
6 | -1 | 1 | ||
7 | 17 | 10 | 10 |
<tbody>
</tbody>
First time I tried to all a table so kindly forgive me if it doesn't work.
The columns for products A, B and C have quantities in the parts rows of how many of each part is needed to build the product.
What I hope you can help me with, is indicate (any way possible), that I should give Product B my attention. This is because I only need to order one part (Part 3) to build it. I don't want to know about Product C because I need to order two parts to build it (3 and 6).
In short, indicate the product columns that are short only one part (rows). It would be handy if one was in a cell so I could change it to a two when I'm looking for products needing two parts ordered.
The formula in the top row will gave quantity 1 above product A. It tells me there's enough stock to build one of product A, but product B in the next columns has enough parts in stock to build negative one products and I should order some parts. Product C can build negative two products.
I sure hope this explanation is clear. This is a very complicated spreadsheet and I left a lot out and I'm hoping it's enough.
Thanks a bunch!
Michele
Last edited: