Forum Discussion

Egemen's avatar
Egemen
Copper Contributor
Oct 07, 2022
Solved

Auto populate the multiple rows if the condition is met

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.   Exam...
excel
Formulas and Functions
  • Lorenzo's avatar
    Lorenzo
    Oct 07, 2022

    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 https://support.microsoft.com/en-us/office/edate-function-3c920eb2-6e66-44e7-a1f5-753ae47ee4f5 & https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929 functions - see attached file

Lorenzo's avatar
Lorenzo
Silver Contributor
Oct 07, 2022

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 https://support.microsoft.com/en-us/office/edate-function-3c920eb2-6e66-44e7-a1f5-753ae47ee4f5 & https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929 functions - see attached file

Egemen's avatar
Egemen
Copper Contributor
Oct 07, 2022
Thank you so much! This was exactly the solution i was looking for!
  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Oct 07, 2022

    Egemen 

    Glad I could help & Thanks for providing feedback

    Just had a look HansVogelaar's option, it's much easier ==> Liked it

    • Egemen's avatar
      Egemen
      Copper Contributor
      Oct 07, 2022

      Thank youLorenzo 

      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))

Resources