linking tabs on a spreadsheet anticipating adding lines and adding missing data

I think you need to amend your sample workbook so your DEADLINES worksheet shows the information the way you want it to appear when automated, then highlight the cells you want to automate, clarify which cells are used to do the lookup. That might make it simpler to suggest formulaic answers.