I have a sheet with "First" and "Last" names. I want to:
Create a column that:
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:
BUT, things are not fine if I:
It doesn't respect that row 2 is frozen and shifts it.
One way to do this:
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?