Survey Roll up


I'm trying to create a Survey that needs to be completed bi-weekly. It's about 130 questions long and on bottom I need the score to calculate. (I'm not using forms.) At the end of the month, I need the average of the last two survey score's and some other data reflected on a dashboard. What would be my best option?

I was thinking of doing the following:

I would need three sheets:

  1. Template Survey Sheet
  2. Survey Sheet
  3. Master Answer Sheet

The idea is that they fill out the answers on the Survey Sheet (For each question they are just choosing a number from 1 - 5). When they complete it, the questions and answers move over to the Master Answer Sheet. Then, I set up an automation that runs bi-weekly that copies the questions again from the Template Survey Sheet on to the regular Survey Sheet. However, any formulas will not move over :( . I can think of workarounds to have the total score calculate on the Survey Sheet (by creating If statements that references entire columns and not specific cells - as once those rows get moved off I get #REF. I would place those formulas in the sheet summary or on top of the sheet and lock the first few rows.

However, the Survey has a few sections using parent/child rows. I would want each parent row to show the total of all the child rows. I can't think of any ideas. If I do a column formula, they can't select/type in a score between 1 -5. If I add a formula to the parent rows, it wont get moved over in my move row automations from the Template Sheet to the Survey Sheet.

Any suggestions or do you have a better way for me to create this Survey??🙄

Please note: I do not have Control Center, Also, I do not want to just take each Survey Sheet and "save as new" each time I need to have the Survey completed. This has to be an automated solution that does not involve human upkeep.