Hello,
Been enjoying learning Coda, although switching off Sheets/Excel logic at times can be tough.
I have a table where the Display Column is people’s names, and each column thereafter is an item they own. The last column is an aggregate which combines all the items each person owns. For example:
Using the List function, I can successfully combine the items from columns Item1, Item2, and Item 3. But, is there an easier way to select all of the “Item” columns? For example, if there were 50 Items columns, it would be tedious to individually type in the name of all corresponding columns in a List function. In Excel/Sheets, this would be akin to B:D to select such a range, but I appreciate that Coda operates on a column-based system.
I am also asking this for dynamic purposes. i.e. If I add an additional “Item” column (e.g. “Item4”), I would want that automatically added to the “AllItems” column.
Bonus points if there is a way to select all columns n places left or right of a specific column. i.e. List all the items from columns 3 to the left of this column.
Thanks in advance for any insight.
1 Like
In case anyone else has the issue or comes across this thread, there is no solid solution that I found. One potential feature/solution would presumably be the ability to name a range. Using the above example, if every “Item” column could be collectively referenced by a range called “Items”, then the code in the AllItems column could be changed from:
List(Item1,Items2,Item3)
to this:
List(Items)
1 Like
Hey Joseph,
I’m trying to solve a similar thing. Imagine selecting a column by something like:
Concatenate("Item","1")
But use this directly as a column name in a function.
I have a slight suspicion that it’s not doable currently. But would be nice to get official confirmation.
I tried testing a column name reference via Concatenate and was not successful. This type of functions feels more like Indirect from Excel. That said, I would defer to the Coda team for any official answer.
1 Like
+1 also looking for this functionality but can’t figure a way of doing this.
This is more of a relational normalisation problem than a Coda problem. You should really have a separate table with two columns: Owner and Item, and then have each item in separate rows. Then it’s just a case of joining and filtering to get the answer you need.
This way you also don’t need to change your table schema and potentially many formulas if you need to add another item; you just have as many item rows as you want. 
/edit - you can also group by item along the top in order to make it look similar to your example.
2 Likes
@Harry_James - as @Nick_Milner said, please convert your schema into two/multiple tables. More often than not, when I find myself applying aggregates over column values, I find myself better off by converting into separate table esp. when each of the column effectively represent same type of entity but different instance/occurrence of it (like in above example, each column is of a type thing | asset | item but a different instance of it) -
another signal to look for is whenever i find myself duplicating text values in different columns - i create a separate table, move values in there and put a “picker” via lookup column instead. this way coda can figure out that values with matching text are one and same )
