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
>Solution :
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)))}