Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Feb 26, 2024 12:06 AM - edited Feb 26, 2024 01:28 AM
Hi all,
I have formulated columns Apple, Orange, Banana, and the formula produces "Y" or "N" values. I want to create another column that will return the name of the column if their value is "Y". Is there any way to do this?
I have tried nested IF formula, like IF(Apple="Y", "Apple", Orange="Y", "Orange"...), but the problem is they just return one value, but I want all the columns as long as their value is "Y".
Thanks in advance!
Solved! Go to Solution.
Feb 26, 2024 12:48 AM
Try this:
IF(
Apple = "Y",
"Apple\n"
) &
IF(
Orange = "Y",
"Orange\n"
) &
IF(
Banana = "Y",
"Banana\n"
)
Feb 26, 2024 12:48 AM
Try this:
IF(
Apple = "Y",
"Apple\n"
) &
IF(
Orange = "Y",
"Orange\n"
) &
IF(
Banana = "Y",
"Banana\n"
)
Feb 26, 2024 05:35 AM
I keep this REGEX formula handy for cases like this:
REGEX_REPLACE(
CONCATENATE(
IF({A}, {A} & ", "),
IF({B}, {B} & ", "),
IF({C}, {C} & ", "),
IF({D}, {D})
),
", $",
""
)
In your case, I think the concatenate part would look like
If(Apple = “Y”, “Apple, “),
If(Orange = “Y”, “Orange, “),
If(Banana = “Y”, “Banana, “),
PS & attribution: I got the formula from a solution that @kuovonne posted here.
Feb 26, 2024 10:02 AM
Thanks for the credit for the formula. But it looks like you linked to my profile, and not where I describe the pattern, which is here.
Feb 26, 2024 10:02 PM
Hi all, thanks for the help!
I'm not too familiar with using REGEX but the IF & formula works - thanks!
Mar 07, 2024 11:50 AM
Hi, I have a similar issue that I am trying to solve. I have twelve columns with various cost options that could be used for purchasing a product. I've created a column with drop downs that match the cost column descriptions. Is there a formula that could be used to select the cost option I'd like to proceed with and have it populate into a new cell? For example, if I choose to proceed with "PROTO LDP 0%- AIR/AIR" could I get the cost of $10 to populate into a new cell?
Mar 09, 2024 10:46 AM
All of those columns would need to be formula columns, and then you would need to have IF statements in all of them that depend on the dropdown column.
Or, if the number is static, you can create a “when a record is updated” automation with a bunch of conditions that puts a fixed number into the appropriate currency field.