Deleted Formulas by Google form entries-CodePudding

I've linked a google form to a google sheets, however whenever there is a new submission, formulas in the google sheet are pushed down. Unfortunately, I need these formulas to pull data into a dashboard.

I've tried an arrayforumla (See below) however, doesn't seem like the right fit.

1st column: (A1) ={"Year"; ArrayFormula(Transpose(A2)="Q"&ROUNDUP(month(B2)/3,0))}

2nd column:(B1) ={"Month"; ArrayFormula(Transpose(B2)IF(D2="",,Date(year(D2),month(D2),1)))}

3rd column:(C1)

={"Year"; ArrayFormula(Transpose(C2)="Q"&ROUNDUP(month(B2)/3,0))}

Please see sample google sheet. https://docs.google.com/spreadsheets/d/1fi0SKpuIrUul_wfEkQlTXX5f5e-xYx1ix-gnGgkVds0/edit?usp=sharing

CodePudding user response：

you are using ArrayFormulas but without arrays. delete everything in C column and use this in C1:

={"Year"; ARRAYFORMULA(IF(D3:D="",,YEAR(D3:D)))}