I’ve built a crop planning calendar in which I enter the date I want a crop to be ready in one sheet, and it is automatically populated in the following sheet using INDEX.
The goal is to enter the date I want the crop to be ready in the first sheet, and the formula in the second sheet “reverse” dates back to the date I need to plant it (which is an earlier date), using:
=IF(U$3+MROUND(($BH4+$BG4)/7,1)>=53, "", IF($C4="Baby", INDEX('TP Bedft'!$D4:$BC4,1,U$3+MROUND(($BG4)/7,1)), INDEX('TP Bedft'!$D4:$BC4,1,U$3+MROUND(($BH4)/7,1))))
On the third sheet is the day on which I’m supposed to seed it in the greenhouse (which is even earlier). However, I not only want the data to move to the right cell (week), but I also want to do an operation on that number to convert it to, in this case, the number of trays to be seeded. Right now I’m using:
=IF('TP Schedule'!U4="","", MROUND('TP Schedule'!U4*$BF4*$BH4/$BG4,0.5))
This changes the number from the second sheet to “# trays”, but how do I do that calculation AND move it to the right cell using INDEX within the same sheet?