Forum Discussion
Solved
Automatically repeating rows based on repeat factor cell (without VBA)
Hello experts, hope you are all well! I have intermediate skills with excel but this one has stumped me. I cannot use any Macros due to workplace policy so this needs to be done with formulas. ...
matt0020190 There are a couple of things with this new layout/structure that I would do differently. Your dates are being entered as Text, which is wreaking havoc with the Duration formula, and the repeated use of VLOOKUP to pull data into the Ongoing Projects worksheet can be simplified with one FILTER formula.
In the attached spreadsheet, I've formatted your Lookup Data as a structured Excel table (Insert > Table), added data validation and conditional formatting to ensure data integrity, and used a different formula to calculate the duration (for more info, see: https://exceljet.net/formulas/get-months-between-dates ).
matt0020190 You expected result data do not match with input data. Just a clue, you may try something like-
=DROP(REDUCE("",D2:D4,LAMBDA(a,x,VSTACK(a,HSTACK(
IF(SEQUENCE(x),EDATE(CHOOSEROWS(B2:B4,ROW(x)-1),SEQUENCE(x)-1),""),
IF(SEQUENCE(x),CHOOSEROWS(A2:A4,(ROW(x)-(MIN(ROW(D2:D4))-1))),""))))),1)
- Hey thanks Harun, this is great!
I cant see where it does not match up, looks fine to me?
Great work!
