Hello all,
Could you please help me find a solution to a problem that i have in Excel? I don't know exactly if this could be achieved in Excel, so any ideea would be highly appreciated.
So, i have a very big production sheet.
- On column B i have the dates (in this format: 01.01.2019, 02.01.2019, 03.01.2019, etc);
- On column C i have the proposed production per day;
- On column D i have the current stock;
- On column E i have the medium request / day
Regarding the fact that the values on column E are changing depending on the client's request, sometimes will be bellow 10 days (out policy is to have a minimum 10 days stock). When this happends, i have to delete everything on column C and replan the production so the values in column E won't be bellow 10.
I was thinking about a formula in column C to do something like this: if E1<10, add 200 in c1. If after this e1 is still smaller than 10, add another 200.
The other problem is that our maximum production capacity / week is, let's say 2000 pcs (in column C), meaning a maximum of 400 pcs / day. So the formula should add 200, on 01.01.2019, then if column E is not 10, it should add another 200, but if column C is still not 10, the formula should go to 02.01.2019 and repeat the steps...
Also, if until the end of the week the value in column E is still not 10, it should repeat the steps begining from next monday (no production on saturday or sunday)
I attached a screenshot of my actual table and another one with the desired result (see links).
Is there any chance i can do something like this with a formula or it can only be achieved with VBA?
I really hope i made myself understood, english is not my native language and as i think you see, i'm not an excel expert...
Thank you so much in advance for any help or ideea you can give me. \Cheers
Actual table
https://drive.google.com/open?id=13Dn8R1IbYgt7XRiy2jj4B6JldN2uJu6U
Desired table
https://drive.google.com/open?id=1Pf9zKgT03JTq4xz9leAN29-5vIcE0ASV
Could you please help me find a solution to a problem that i have in Excel? I don't know exactly if this could be achieved in Excel, so any ideea would be highly appreciated.
So, i have a very big production sheet.
- On column B i have the dates (in this format: 01.01.2019, 02.01.2019, 03.01.2019, etc);
- On column C i have the proposed production per day;
- On column D i have the current stock;
- On column E i have the medium request / day
Regarding the fact that the values on column E are changing depending on the client's request, sometimes will be bellow 10 days (out policy is to have a minimum 10 days stock). When this happends, i have to delete everything on column C and replan the production so the values in column E won't be bellow 10.
I was thinking about a formula in column C to do something like this: if E1<10, add 200 in c1. If after this e1 is still smaller than 10, add another 200.
The other problem is that our maximum production capacity / week is, let's say 2000 pcs (in column C), meaning a maximum of 400 pcs / day. So the formula should add 200, on 01.01.2019, then if column E is not 10, it should add another 200, but if column C is still not 10, the formula should go to 02.01.2019 and repeat the steps...
Also, if until the end of the week the value in column E is still not 10, it should repeat the steps begining from next monday (no production on saturday or sunday)
I attached a screenshot of my actual table and another one with the desired result (see links).
Is there any chance i can do something like this with a formula or it can only be achieved with VBA?
I really hope i made myself understood, english is not my native language and as i think you see, i'm not an excel expert...
Thank you so much in advance for any help or ideea you can give me. \Cheers
Actual table
https://drive.google.com/open?id=13Dn8R1IbYgt7XRiy2jj4B6JldN2uJu6U
Desired table
https://drive.google.com/open?id=1Pf9zKgT03JTq4xz9leAN29-5vIcE0ASV
