I have a restricted dropdown menu that based on the choice made, a value is added in helper column

The restricted dropdown options are

P1a

P2a

N/A

The value for P1a is 2.0, the value for P2a is 1.5, and no value if the dropdown is left blank or N/A is selected.

I have been using the following formula when the values were the same (both 1.5) =IF(OR(ISBLANK([Wednesday, 1/10/24 Opening Keynote 1:45-2:45 pm]@row), [Wednesday, 1/10/24 Opening Keynote 1:45-2:45 pm]@row = "N/A"), "", 1.5)

but since each choice has a unique value, this formula will not work

I tried using =IF(OR(CONTAINS("P1b", [Wednesday, 1/10/24 Pre-Conference 9:30-11:30 am]@row), 1.5), (CONTAINS("P1a", [Wednesday, 1/10/24 Pre-Conference 9:30-11:30 am]@row), 2.0) but I'm getting a syntax error.

I appreciate the help!

Find more posts tagged with

Accepted answers

=Chris Palmer

Hello @pris

Try this formula and let me know if it works for you:

=IF(CONTAINS("P1a", [Wednesday, 1/10/24 Opening Keynote 1:45-2:45 pm]@row), "2.0", IF(CONTAINS("P2a", [Wednesday, 1/10/24 Opening Keynote 1:45-2:45 pm]@row), "1.5", "N/A"))

=Chris Palmer

Something in your sheet must be amiss, I created a test environment to replicate this and it worked perfectly:

All comments

=Chris Palmer

Hello @pris

Try this formula and let me know if it works for you:

=IF(CONTAINS("P1a", [Wednesday, 1/10/24 Opening Keynote 1:45-2:45 pm]@row), "2.0", IF(CONTAINS("P2a", [Wednesday, 1/10/24 Opening Keynote 1:45-2:45 pm]@row), "1.5", "N/A"))

pris

@Mr. Chris I tried it and it changed everything to N/A regardless of the choice that was selected

=Chris Palmer

Something in your sheet must be amiss, I created a test environment to replicate this and it worked perfectly:

pris

Would it matter if the dropdown is restricted to the values in the list? @Mr. Chris

=Chris Palmer

I have mine set up to restricted values as well:

Have you verified the column names in the formula match the column names you are referring to in your sheet to be exact?

pris

Actually it worked, I was referencing the wrong column and that is why I was receiving the error! Thank you so much for your help! @Mr. Chris

pris

@Mr. Chris So it worked and the values are populating correctly, however, when I sum the figures of all the helper columns to get the total, it does not count the value in this column so my totals are either off by 1.5 or 2. Do you know why this is? All my other helper columns are correctly added together.

pris

I removed the the quotations from the value and it fixed the issue, thank you for your help!

=Chris Palmer

https://community.smartsheet.com/discussion/comment/412042#Comment_412042

Excellent!

Happy it's working for you.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.