hi,


here is the situation. in "sample 1" file i need to change date and shift based on previous cell date. currently i managed to if A3 has date "DATE: 06/06/2023" then A20 will be "DATE: 06/06/2023" and if a3 has date "DATE: 06/06/2023" then A20 will be "DATE: 07/06/2023" and so on. it depends on "shift" as you can see i have to check it manually if there is production in both shifts of the day. if not i have to hide the entire row of page of that shift. if suddenly no production on a given date i change in manually then rest the dates goes following that date. shift formula and date formula is separated in sample 1 A20, A21 and so on.


And shift cell A21 is simple, if A4 is "SHIFT : A" then A21 is "SHIFT : B" & A4 is "SHIFT : B" then A21 is "SHIFT : A".




here is what i want to do-

if A3 has date "DATE: 06/06/2023" & A4 is "SHIFT : A" then A20 will be "DATE: 06/06/2023" because A20 page is the same day but different shift.


if A3 has date "DATE: 06/06/2023" & A4 is "SHIFT : B" then A20 will be "DATE: 07/06/2023" because its the next day another shift.


now i want to combine this functions. so i tried the formula in "tried formula.docx".


it gives "more than 64 level nesting" error while i paste it.


please help me. i tried best i could to explain. please use the excel file to understand the situation more.

Attached Files

	sample 1.xlsx (178.0 KB, 12 views)
	tried formula.docx (12.7 KB, 5 views)