I have a sheet with "First" and "Last" names. I want to:
Create a column that:
- Concatenates first, space, last
- Automatically fills in value if new rows are added
- Allows filtering and sorting of data (ALL ways)
| First | Last | Full (formula) |
|---|---|---|
| Sam | Welsh | Sam Welsh |
| Sandra | Day | Sandra Day |
| Pete | Jay | Pete Jay |
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:
- Select data
- Data > Create a filter
- Click filter icon by a column header
- 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?
