How to update one column based on values in another column?

Hi friends. I tried searching for this but couldn’t find an example that’s exactly what I’m after. Please forgive what might be an unintelligent question. I’m not the best at being fancy in Coda yet, but I’m aspiring! :blush:

I’m working with lead lists for a sales team. We use tiers to decide which rep should work what, and we assign the tier based on the employee count of the prospect. A Tier 1 account, for example, would have 1000+ employees, while a Tier 2 account would have fewer than 1000 employees.

I’d like to set up some logic so that if the value in the Employee Count column is >1000, the formula will populate the number 1 in the Tier column (and if <1000, it will put 2 in the Tier column). I can’t seem to find the right way to do this!

Similarly, I need to create essentially the same formula but for text values. So if the name Adam gets entered into the AE column, the SDR column gets updated with the name Eve (bonus points if I can make that relationship bidirectional, so the SDR column saying “Eve” also makes the AE column populate with “Adam”).

Any help would be so appreciated! I think if I can work this out, I’ll be able to make our Coda tables smart enough to get some time back for my sales ops person. :pray:

It’s Friday and I am about to go to the bars - so my response won’t include an example. But you want to use switch if … look at the formula docs under resources or search community for switchif examples. Its super simple

HI

In this thread are two more ways to accomplish this.

Formula Question: How To Convert Phrase to Number.

The switchif and substitute methods do away with the extra table, at the expense of formula complexity. The filter formula adds a table, but is easy and visible to maintain, with a very simple formula that does not need to be changed when new entries are made.

Regards
Rambing Pete

1 Like

Wow - thank you! It took a little bit of fiddling, but I managed to make this work. This is perfect for substituting exact values for other exact values. I’m curious how you would go about doing something like this:

Tier 1s have 1000+ employees and Tier 2s have 500-999 employees. How can I make an “Employee Count” column with values like 588, 972, 1235 all trigger the Tier column to display the correct tier? (In the example, 2, 2, 1 respectively)? I’m finding the SwitchIf method difficult to understand, perhaps because I haven’t found an example that feels similar to mine that I can replicate and play around with.

Can Coda understand numbers within a specified range easily or is this going to require a lot of complicated formula work?

Thank you again so much for your help!

hi @cmarie ,

I would like to suggest to make use of a filter, like the one below

in the example I reference (link to) the text box that contains a value, but of course you can have a column in your table as starting point as well.

I wrote about this about a year ago, details in the part ‘larger numbers’.

Hope it helps to move forward with Coda!
Cheers, Christiaan

3 Likes

Clever trick!

I would have done a if X > 500 AND X < 1000, which would have been nasty.

P

bonus points if I can make that relationship bidirectional, so the SDR column saying “Eve” also makes the AE column populate with “Adam”

Not possible :confused:

Shameless suggestion box plug: Bi-directionally synced formula columns

As for the Tiers, you want something like this as a formula in your “Tier” column:

SwitchIf([Employee Count] > 1000, 1, [Employee Count] > 500, 2, [Employee Count] > 100, 3, 4)

This supports four tiers (can easily add more). Otherwise for only two tiers, something like If([Employee Count] > 1000, 1, 2) is sufficient.

1 Like

HI,

I missed the second part of your question, about Adam and Eve. Can you provide more information about the requirement?

I.e. how does the system know that Adam and Eve belongs together?

Regards
Piet

1 Like

This is very helpful, thank you so much! Seeing this in action makes it click so much faster.

Very happy to vote for this!

And this example of SwitchIf in action is great - I will play around more with it. Thank you!

My sales ops coordinator is grabbing data from our CRM for me so we can build some account lists in Coda. Adam and Eve always work together on deals. Adam or Eve could be the last person to have contacted a company, so when she sees either one in the CRM, she can simply input whichever one she sees into the Coda table right away. This association would help her move faster so she doesn’t have to recall that Adam and Eve are always linked from memory. It’s definitely a nice-to-have, not something super important, as we could always edit the columns in bulk later.

How would Coda know that Adam and Eve goes together?

Do you have a table that says Comp A is supported by Adam and Eve, and Company B is supported by Cain and Able?