Referencing a variable column header

#1

I am trying to reference a column from another table, where this column depends on the value of the row in the current table
Basically something like that.
table1.[thisRow.Value].sum()

At the moment I have succeeded with numerous switchif cases but this is becoming unmanageable as the table expands:
SwitchIf(thisRow.Value=“value1”,sum(table1.[value1]),thisRow.Value=“value2”,sum(table1.[value2]),thisRow.Value=“value3”,sum(table1.[value3]))

It also means I need to edit the formula each time a new row is added to the table.

Is there any neat way to do all that in one go?

0 Likes

#2

It’s hard to say without seeing the table, but my guess is that it would help if you could transpose the lookup table so you could filter by row instead of by column.

If you want to share a sample document, we can look into it further and try to come up with some solutions. Check out this article on sharing documents and look for the “Share and Play” option…

…and welcome to the Coda Community! :slightly_smiling_face:

0 Likes

#3

Of course, here is a sample doc. This should clarify things

the formula for sum of shapes is the one I want to simplify :slight_smile:

1 Like

#4

Hi @Eric_Munier - welcome!

What @benblee said - transposing the data will make it much more manageable and help you get the most out of Coda.

Take a look at https://coda.io/d/Copy-of-Untitled_d7OiZlXjQk1/Section-1_suByr#_luy5M where I have shown how the transpose would work.

And also Filter created by to a summary table to look at ways to get a summary table going.

2 Likes

#5

Thanks so much for looking into that guys.
@mallika. In you example how would I generate table 3 easily from table 2?

In my simplified example it can be done manually but in my real doc, my table 2 expands constantly both in rows and columns.
That table 2 is already dependant on other tables so I can’t use a transposed version of that table a base. And lets also mention not all columns are numbers.
I need to keep that table 2 as is, for parts of the doc to work.

Does that make sense?
Any additional help would be appreciated :slight_smile:

0 Likes