I'm trying to get the data in rows to move up or down based on if col A grows or shrinks. However col A's data is defined by the range I want to shift. I haven't been able to find something that looks right and was hoping someone could point me in the right direction.
For some context here is the formula that fills in Col A. This formula is in cell A5. =UNIQUE(FILTER(FLATTEN(B2:E), FLATTEN(B2:E)<>"x", FLATTEN(B2:E)<>""))
| a | b | c | d | |
|---|---|---|---|---|
| name | namea | nameb | namec | named |
| word | worda | |||
| number | ||||
| namea | x | nameab | ||
| nameb | ||||
| namec | ||||
| named | ||||
| worda | ||||
| nameab | nameaba |
Essentially as I add strings to the range B2:E, it adds it to the A column. The problem is that I'm filling in the data from B2:E myself as I find it. When I insert wordb to cell C3, it gets inserted into the A col after worda and before nameab (cell A10). I would like the contents of the whole row 10 to be shifted down to stay inline with nameab in the A column. I would also like it to move up if an item is deleted (say I typo an entry and delete it). I'm aware that this might cause data loss and I'm not sure how to handle that yet.
You could also think about it as Col A being a "Header" column the same way that Row 1 is acting as a Header too. I want to lock the row from B to E to the value of the cell in the A column. I.e. I want nameab and nameaba to always be in the same row even as things get added above nameab.
Thanks for any and all help.