I am using a Google Form to collect data into a Google Sheet, I made another sheet and want to fetch data to this new sheet when a new form is submitted.
Used =QUERY('Form responses 1'!A228) to get data from the Form responses 1 sheet and it works good. I dragged the cell and the formula get copied correctly to the lower cells like =QUERY('Form responses 1'!A229) =QUERY('Form responses 1'!A230) and so on.
But now when a form is submitted nothing gets updated in the new sheet, I noticed the cell address has somehow skipped a place and when I do a new drag and drop from top column everything works fine. The cell references are copied correctly so that it should work on new form submit, but as soon a form is submitted the next row formula gets omitted, like the row got deleted in new sheet. i.e. After form submit if the current row formula is =QUERY('Form responses 1'!A228) and net row formula WAS =QUERY('Form responses 1'!A229) it gets changed to =QUERY('Form responses 1'!A330) and no row gets updated in the new sheet.
Try just ={'Form responses 1'!A228:A} . This is a formula that will bring in every cell from A228 to the bottom of the sheet. This way you do not have to keep dragging down formulas.