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 Oct-2024.

 

 

How can I have the SEQUENCE to just start on a specific column? I already have another field in this table with the month that should start, so I could check if it is the same as the column, but I don't know how to achieve it.

Thanks for helping.

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor

    Hi K_PR24 

     

    Not top clear to me so this might not be exactly what you expect. As you can see below I used 3 columns on the left to define the parameters per row: When to Start; For how many Months; The value for each month. With Dates in D1:X1:

     

    in D2:

    =IF((D$1:X$1 >= A2) * (D$1:X$1 < EDATE(A2, B2)), SEQUENCE(,B2 +XMATCH(A2,D$1:X$1) -1, C2,0), "")
    • 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

        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