To start, you should never add anything, manually or via formula, to form intake sheets. It nearly always leads to problems. Leave the raw form data alone. That said, my first suggestion is that you delete not only your additional headers and formulas in sheet 'A' (the form intake sheet as currently named), but that you delete all columns other than those being used by the form intake fields. To do this, click on the 'E' at the top of Column E, scroll over to Column L, hold down the Shift key, and click the 'L' at the top of Column L. Right-click and choose "Delete columns E-L."
Next, as you will see, I've set up a new sheet called "Erik Help." You can rename this to whatever makes sense to you. Currently, this sheet is protected such that only you and I can access it. You can change that as you wish.
In this sheet, you'll notice that formulas only exist in Row 1. These formulas are array formulas, each of which generates the header and all results for the respective column. This is the first key to dealing with processing incoming form data without problems.
Second, you'll notice that each formula references the entire column from sheet 'A' (e.g.,
C:D, etc.). That is, you will not see any references like
A2:A. This is the second key to dealing with processing incoming form data. If you use references other than whole-column references, as soon as new rows are added to the form sheet, references like
A2:A self-adjust to become
A3:A and so on.
Finally, you'll see that there are basically two kinds of formulas used in my sheet.
This type simply brings over the entire column as-is from the form sheet. If you want to bring over contiguous columns, you can do that (as I've done in Column F of my sheet where the formula is
This is a processing formula. It changes something about the raw form data. Notice a key setup. In order to form a header and still reference the entire raw data column, we use this:
This says, "Only if the raw-data row is Row 1, enter this string in cell 1 (which becomes the new header).
If it's any other row than Row 1, something different will happen to every row in the range from Row 2 down. In the case of the example given, this is what will happen for each of those other rows:
For any raw-data row in the referenced column that is blank, this part of the formula will leave the corresponding cell in that row blank as well (that is the two commas side by side, which essentially means "do nothing"). For any raw-data row in the referenced column that is not blank, the INT value of the original data will be output to the corresponding cell. (In the case of date/time, this will reduce them to date only.)
Every formula in my sheet follows from these two examples.
Note that all references that create the header or check for blanks should be to the same sheet as the final portion of the formula, because all ranges have to be the same length. In this second formula above, the final part of the formula is
INT(A!A:A), so we reference
A!A:A everywhere else in the formula. But in another formula (cf., the formulas in Columns D and E), the final comparison is to something in the same sheet, so the header and blank-check portions are also to the current sheet, not the raw-data sheet. In sheets that have exactly the same number of rows at all times, this wouldn't matter. But if the raw-data and processing sheet vary by even as much as a single row, formulas that referenced the two different sheets within themselves would begin to error due to the mismatch between column lengths.