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!
Best Answers
-
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"))
-
Something in your sheet must be amiss, I created a test environment to replicate this and it worked perfectly:
Answers
-
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"))
-
@Mr. Chris I tried it and it changed everything to N/A regardless of the choice that was selected
-
Something in your sheet must be amiss, I created a test environment to replicate this and it worked perfectly:
-
Would it matter if the dropdown is restricted to the values in the list? @Mr. Chris
-
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?
-
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
-
@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.
-
I removed the the quotations from the value and it fixed the issue, thank you for your help!
-
Excellent!
Happy it's working for you.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 302 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
