Forum Discussion
Production Timeline
Mike_Byrum I put together the following sheet that I think does what you want. I used this formula:
=IFERROR(1/(1/SUMPRODUCT($B$3:$BA$3*(MOD($B$1:$BA$1+$B$4:$BA$4,52)=B1))),"")to calculate the number of mature plants that week. I added the 1/ 1/ to create an error so cells that would should 0 actually show blank. I could have also just changed to a custom number format but that sometimes confuses people.
I put the mature count above so they can be 'locked' and you could add more below, just felt right to me. I assumed the 52 weeks would roll over because you didn't say otherwise but that can cause issues when you still have plant info from early wks and then plant info in later weeks pointing at the "same week" but actually 2 different years.
So in the image you can see wk 1 has plants maturing in wk 3.
wks 7 & 12 have plants maturing in wk 15
wk 8 has a plant maturing in over a year and wk 19 in 1 wk but it looks like all 17 mature in wk 20
see attached file.
With your permission, if I can recommend. It can help us all if you upload an Excel file (without sensitive data), no picture. Even if it is said that a picture can say a thousand words, it is certainly not in the case of Excel, on the contrary in some cases. You could get a precise solution much faster with a file (w/out sensitive data). This would also be a blessing for all of us, as we can understand the problem much better, a win-win situation for everyone.
*Knowledge of Excel version and the operating system is a must have if you want to proposing a reasonable solution (Example: office version e.g. 2016 or 2019 or 365 web or 365 pro, etc) and your operating system (e.g. Win10 (2004), Win 10 (1903), Mac, etc.).
In order not to be misunderstood, in the community it is the aim of excel users to give help on their project (from Excel users), or rather, to provide food for thought and not to deliver finished work or finished ideas.
Thank you for your understanding and patience
Nikolino
I know I don't know anything (Socrates)
I appreciate your help and the information you offered. A man who hopes to discover gold needs more than someone to show him the characteristics of a good potential sight for a mine. He also needs someone to show him how to get to the area where the potential mines are located not to mention the tools he will need. Next time I will be sure to take all of the right tools with me, thanks to you:).
I am glad that you were able to find a solution with the help of Mr. mtarler
We cannot do great things - only small ones, but those with great love.
Mother Teresa©
Thank you for your patience and time.
Wish you a nice day / night with lots of health, joy and love.
Nikolino
I know I don't know anything (Socrates)