0

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

=sort(Formdata!B$2:B,1,TRUE)

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:

=sort(Formdata!B$31:B,1,TRUE)

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

Improve this question

1 Answer 1

Reset to default
1

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.

Improve this answer
9
  • 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/…
    – Kenneth Poulsen
    Commented Jul 13, 2020 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
    – marikamitsos
    Commented Jul 13, 2020 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
    Commented Jul 14, 2020 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?
    – Kenneth Poulsen
    Commented Jul 14, 2020 at 6:06
  • Also, is there a better way to handle column E? Works but looks messy :/
    – Kenneth Poulsen
    Commented Jul 14, 2020 at 6:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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