Hello. I work in a factory, and we build a variety of products. We will call them products A through
We plan our builds based on shipment day. The problem is, not everything takes the same amount of time to build.
We can plan to ship the products based on customer need dates. That is what the top chart is, ship day per product. The flow is backed off from there to show what day each section needs to build their part.
Our factory can only support 500 pieces per day total. Since every product has different flows, we can plan that 500 based on the ship date but as the different flows back off, it causes over-capacity issues like are shown in the second chart- with a primary concern of our machine shop. As you can see, in the top chart we never build more than 400 per day but because the machine backoff dates are different, that product may hit the same shop on the same day.
'
What I would like to do is to use a formula (vba is not allowed at work) to say product A has a 4-day backoff, so go to the table above, find the row for A, and pull the data from 4 cells over. I don't want to manually code it like is in the example where it just references above. I would like it to be a formula that will match the product code, then go count over the number of cells in the Machine Backoff Days. That way, if flows change, products are added, etc., it will still work. Hopefully this makes sense and it is a possibility! Thank you for any help you can give.
We plan our builds based on shipment day. The problem is, not everything takes the same amount of time to build.
We can plan to ship the products based on customer need dates. That is what the top chart is, ship day per product. The flow is backed off from there to show what day each section needs to build their part.
Our factory can only support 500 pieces per day total. Since every product has different flows, we can plan that 500 based on the ship date but as the different flows back off, it causes over-capacity issues like are shown in the second chart- with a primary concern of our machine shop. As you can see, in the top chart we never build more than 400 per day but because the machine backoff dates are different, that product may hit the same shop on the same day.
'
What I would like to do is to use a formula (vba is not allowed at work) to say product A has a 4-day backoff, so go to the table above, find the row for A, and pull the data from 4 cells over. I don't want to manually code it like is in the example where it just references above. I would like it to be a formula that will match the product code, then go count over the number of cells in the Machine Backoff Days. That way, if flows change, products are added, etc., it will still work. Hopefully this makes sense and it is a possibility! Thank you for any help you can give.
| cell shift.xlsx | |||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| B | C | D | E | F | G | H | I | J | K | L | M | N | |||
| 1 | Ship Date | ||||||||||||||
| 2 | Machine Backoff from Ship Days | Product Code | 20-Nov | 21-Nov | 22-Nov | 23-Nov | 24-Nov | 25-Nov | 26-Nov | 27-Nov | 28-Nov | 29-Nov | 30-Nov | ||
| 3 | 4 | A | 200 | ||||||||||||
| 4 | 4 | B | 300 | ||||||||||||
| 5 | 4 | C | 400 | ||||||||||||
| 6 | 5 | D | 100 | ||||||||||||
| 7 | 6 | E | 200 | ||||||||||||
| 8 | 7 | F | 200 | ||||||||||||
| 9 | |||||||||||||||
| 10 | |||||||||||||||
| 11 | Machine Load Date | ||||||||||||||
| 12 | Machine Backoff Days | Product Code | 20-Nov | 21-Nov | 22-Nov | 23-Nov | 24-Nov | 25-Nov | 26-Nov | 27-Nov | 28-Nov | 29-Nov | 30-Nov | ||
| 13 | 4 | A | 200 | ||||||||||||
| 14 | 4 | B | 300 | ||||||||||||
| 15 | 4 | C | 400 | ||||||||||||
| 16 | 5 | D | 100 | ||||||||||||
| 17 | 6 | E | 200 | ||||||||||||
| 18 | 7 | F | 200 | ||||||||||||
Sheet1 | |||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| H13,F15,G14 | H13 | =L3 |
| F16 | F16 | =K6 |
| F17 | F17 | =L7 |
| F18 | F18 | =M8 |
