Google Sheet: Want formula to auto-fill in new rows and allow data sorting

I have a sheet with "First" and "Last" names. I want to:

Create a column that:

1. Concatenates first, space, last
2. Automatically fills in value if new rows are added
3. Allows filtering and sorting of data (ALL ways)

I made the following array formula:

=ARRAYFORMULA(A2:A&" "&B2:B)

and put it in 2C. (row with Sam)

That works until I sort the data. If the original row with the formula gets moved, only rows beneath that row use the formula.

OK, so I added a new blank row beneath the header row and put the formula there (2C). I then froze the first two rows (header and formula row).

Everything is fine if I:

• Sort by hovering in Cell C and clicking sort triangle.
• Sort by Data > Sort range (or sheet)

BUT, things are not fine if I:

• Sort using sort option in Filter menu.

It doesn’t respect that row 2 is frozen and shifts it.

One way to do this:

1. Select data
2. Data > Create a filter
3. Click filter icon by a column header
4. In the filter menu, choose one of the Sort by options.

I can’t just not sort that way because this is a sheet that will be used by multiple people.

I’ve tried protecting the row, but then you can’t filter, at all.

It seems like a bug that Sorting via the Filter menu does something different than sorting via other menus. Is there a way around it?

>Solution :

Enter the formula in C1 instead of C2:

={"Formula";ARRAYFORMULA(A2:A&" "&B2:B)}