Auto populate the multiple rows if the condition is met

New Contributor

Hi everyone,

I'm currently working on a recruitment forecasting file.

The idea is when someone enters 1 to any cell, excel should populate the possible timeline for the recruitment plan.



If July 2023 is the possible start date

April, May, and June 2023 are the training period

March is the notice period

February is interviews

The job ad posting is in January


How can I achieve this?


Please see the excel file and thank you so much for the support!


7 Replies
best response confirmed by Egemen (New Contributor)

Hi @Egemen 




Dates (and not Text values like Dec, Jan...) must be enterred in row 10. You can Custom format them to only display short month names if you want


All Cond. Format. rules, but for Commencement, done with EDATE & XLOOKUP functions - see attached file


See the attached version.

Thank you so much! This was exactly the solution i was looking for!
Thank you so much Hans!


Glad I could help & Thanks for providing feedback

Just had a look @Hans Vogelaar's option, it's much easier ==> Liked it

Thank you@L z. 

How can I change this formula for to look ahead to the next three months from the commencement date?

I tried to change it but had no luck.

Thanks a lot again.
=(E$10:AB$10 >= EDATE(XLOOKUP(1,E11:AB11,E$10:AB$10),3)) * (E$10:AB$10 > XLOOKUP(1,E11:AB11,E$10:AB$10))

@Egemen. In grey in the attached file