Saturday, April 28, 2018

yogi_Auto-populate table with dynamically-generated row duplicates

Google Spreadsheet   Post  #2436

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Apr-28-2018

question by: Thibaut Delarbre
https://productforums.google.com/forum/#!topic/docs/XSbRtuAp_Yc;context-place=forum/docs

Auto-populate table with dynamically-generated row duplicates
Hi everyone,

I'm trying to automatically create rows in a table (n°2) based on a "row template" defined in another table (n°1).  In table n°1 I define 3 variables that are used to create the "copies"  
1) Start date: The date of the first copy
2) Cycle: Used to define the date of the following copies. For example if Start date = 1-Jan-2018 and Cycle = 1 month, date of the following copy should be 1-Feb-2018
3) Repeat: Number of row copies. 

I've made the example spreadsheet, which includes the expected output "hard-coded", i.e without formulas. It would be great 
if you could add the formula for the dynamically-generated output in table n°3. Thank you!



EDIT 25-April:  I've found at this thread which gives great inspiration and used it to come with a solution in sheet "Solution1". However that solution has several shortcomings which I would love your help to fix! 
a) I had to use several inelegant "support" columns
b) These extra columns don't auto-expand with arrayformula, meaning I'll have to drag and drop every now and then
c) It's using vlookup, which I don't particularly like because the columns indexes in {1,2,3}. are "hardcoded" and won't update dynamically if I move columns around