Forum Discussion

K_PR24's avatar
K_PR24
Copper Contributor
Jul 03, 2024

Dynamic SEQUENCE

Hello Community,   I am looking to have a SEQUENCE populating the values from a specific column. Example below: The line one should start in Jan-2024, while the second in Sep-2024 and the third on...
Excel
Formulas and Functions
K_PR24's avatar
K_PR24
Copper Contributor

Hi Lorenzo 

 

Thanks for sharing your experience. My rule currently is the following: =IF(L2=AZ1#, TEXT(SEQUENCE(1,VLOOKUP(Y2,Table9[#All],4,0)),"")&AA2, TEXT(SEQUENCE(1,VLOOKUP(Y2,Table9[#All],4,0)),"")&AA2)

 

The problem is that it is not starting populating from the column/month that I want. Each line is supposed to start the sequence in a different column (month). Any hints? Thanks.

Lorenzo's avatar
Lorenzo
Silver Contributor
Jul 04, 2024

Hi K_PR24 

 

Each line is supposed to start the sequence in a different column (month). Any hints?

With formulas I don't see an option. With Power Query, probably... but that won't be dynamic (a query can be configured to auto-refresh every n minute(s) though). If you're interested could you share a sample file as I'm not quite clear on your setup?

 

BTW, interesting to note you use VLOOKUP, not XLOOKUP

  • K_PR24's avatar
    K_PR24
    Copper Contributor
    Jul 04, 2024

    Hi Lorenzo 

    Thanks for taking the time to reply.

    I created the following formula, which is populating from the expected month, but only that month, not the following ones:
    =IF((AZ$1:BW$1 = $L2),SEQUENCE(1,VLOOKUP($Y2,Table9[#All],4,0),$AA2,0),"")

    If I try to use the >=, it mess up the values:

     

    I noticed that the issue is with the IF function, but I am not sure how could I do different and still get the result I am looking for.

    Thanks,

    KP

    • K_PR24's avatar
      K_PR24
      Copper Contributor
      Jul 09, 2024
      I have managed to create the following formula to solve my problem and now it works perfectly:

      =IFERROR(IF(Tables!$E$11:$AB$11 >= DATE(YEAR(K2),MONTH(K2),DAY(K2)+28),SEQUENCE(1,VLOOKUP($Y2,Table9[#All],4,0)+IFERROR(DATEDIF(TODAY(),DATE(YEAR(K2),MONTH(K2),DAY(K2)+28),"m"),0)-IFERROR(DATEDIF(DATE(YEAR(K2),MONTH(K2),DAY(K2)+28),TODAY(),"m"),0),$AA2,0),""),"")

Resources