I need a formula or macro to do reverse planning in my file

[pollito]

Hello everyone,

I have been in the forum for a long time and I have learned some things for my excel sheets, but I cannot do what I need now.

I have a sheet in which I load items of products on line 5 per week and what I need is to have a macro that goes through all lines 4-3-2-1 these items, depending on the times (in days) indicated on the Date sheet you have for each item.
In the calculation of the macro, Saturdays and Sundays, and also holidays, must be discounted.

Is this possible?

Thanks,

Fernando

Hello,

What I'm trying to do is reverse planning.

For example:

In Line 5 I have a product to manufacture in a certain week and knowing the lead time of the product in each line, I can determine the week of completion in each of the lines 4-3-2-1.

Thank you

Fernando

[JeteMc]

Are the green highlighted values in rows 1028:1042 on the production sheet the expected results.
If so, please tell us why they are expected.
If not, please show us some expected results along with an explanation of why they are the expected.
Remember that you are the expert about what you are attempting, so supply enough details that someone with no prior knowledge of the way your production lines work can understand.

[pollito]

Hello JeteMc,


The data highlighted in green is data that I know and what I need is to calculate from those weeks when I should deliver on each line (line 4 -> line 3 -> line 2 --line 1).

For this I have the times of each product in each line, for example for the product (item = Z0011, the production time in Line 1 = 4.9 days, for Line 2 = 2.30 days, for line 3 = 1, 70, for Line 4 = 2.00 days, for Line 5 = 1 day. From the weeks:

Item Wk12 Wk13 Wk14 Wk15 Wk16 Wk17 Wk18
Z0011 5 2 1 1 1


With the times indicated calculate from Line 5 to Line 1.

You have to consider that each week you only work from Monday to Friday and it is also necessary to subtract holidays.

Thank,

Jose

[JeteMc]

Sorry, I still have no idea of what you want to see or where you want to see it.
I feel that the best way to communicate your request would be to upload another .xlsx file manually showing some of the results that you want a formula/code to automate.
Let us know if you have any questions.