Why does my formula change, when I insert new rows into the source sheet (Formdata)?


The first row in Formdata is a header.

From time to time I need to empty the Formdata sheet, but the "function" change when I start to insert new data. Example:


How can I make it, so it always start from the 2nd row? (excluding row 1, the header)


Kenneth. It is always best to share a link to a sample sheet, being sure to set permission to "Anyone with the link can edit." However, I can tell you that you're going to want your sorting formula to reference the entire column B, not just B2:B. Even with the $ anchor in place (i.e., B$2), when rows are added or deleted, that reference will change.

Again, I don't know how your sheet is set up. But try deleting the entire column where you currently have your formula =sort(Formdata!B$2:B,1,TRUE) — including the header — and place the following QUERY in the top cell of that column instead:

=QUERY(Formdata!B:B,"Select * Where B Is Not Null Order By B Asc",1)

If you want a different header for your results column than you have in your source column, just modify the end of the QUERY like this:

=QUERY(Formdata!B:B,"Select * Where B Is Not Null Order By B Asc Label B 'My Header'",1)

If you really want to stick with SORT, you can use this (again, in the top cell of a column):

={"My Header";SORT(FILTER(Sheet4!B:B,Sheet4!B:B<>"",ROW(Sheet4!B:B)<>1),1,TRUE)}

If these do not do what you expect them to do, please share an editable link to a sample sheet.

  • Thanks for the reply Erik. I got the query to work, or some of it. The query include the header (row 1), I dont want that. I can't delete the columns, because its a part of the connected form. Here is a link: docs.google.com/spreadsheets/d/… Jul 13 '20 at 21:04
  • In cell A2 you can use =QUERY(Formdata!B2:B,"Select * Where B Is Not Null Order By B Asc",0). Make a note that we use the open range Formdata!B2:B instead of Formdata!B:B Jul 13 '20 at 23:59
  • Kenneth, I added a duplicate sheet where I placed my second formula into cell A1 and D1 (changing only the Formdata! column reference to match what you wanted). If this is not what you expect, please explain with more clarity what is not as you hoped.
    – Erik Tyler
    Jul 14 '20 at 0:28
  • Thank you very much - works exactly like I wanted, you're a champ :) So what I should have done was to add the query to the top row and adding a label to the query. Adding the last parameter ("1") to the query, tells it to skip the first row. One last question though (hope that is okay): How can I limit column A and B to only include Formdata of the last 2 entries by that person (limit by character name). Example, Jesper has made 3 form submits, but I would like to limit this to only include the latest 2. How can I do this? Jul 14 '20 at 6:06
  • Also, is there a better way to handle column E? Works but looks messy :/ Jul 14 '20 at 6:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.