Basically, you can see clients on the left and then different stages of the project next to it. These dates are manually entered. The "(IR)" is basically our internal review and has to get priority if it's in the same week as the same stage without "(IR)"
Because it has to get priority I did a basic nested if that goes one by one and sees if the week is an IR week and if it is, show IR
After that, if a week is not an IR week, I did a LOOKUP to find what phase that date would fall under. If it was an IR date, that means we already have that IR listed, so I just put the stage without IR.
It's pretty much working but it's very unnecessarily completed I feel...
Here is the formula from cell L3:
Code:
=IFERROR(IF(AND($C3>=L$1,$C3<=L$2),$C$2,IF(AND($E3>=L$1,$E3<=L$2),$E$2,IF(AND($G3>=L$1,$G3<=L$2),$G$2,IF(LOOKUP(L$1,$B3:$K3,$B$2:$K$2)=$C$2,$D$2,IF(LOOKUP(L$1,$B3:$K3,$B$2:$K$2)=$E$2,$F$2,IF(LOOKUP(L$1,$B3:$K3,$B$2:$K$2)=$G$2,$H$2,LOOKUP(L$1,$B3:$K3,$B$2:$K$2))))))),"")(Note: there is a formula in column K that just adds 7 to the END date so that it doesn't keep repeating END END END after a project is done)
Problem:
Look at cell AA5. This cell should "5" as phase 5 starts on 9/1 and that is in the week. However, the LOOKUP function moves over one and puts it into the next week. How can I fix this?
Is there just a way easier way to do this overall?
Thank you for reading, helping
