Auto populating a cell value based on potentially two different columns
Hello experts! New question, hopefully another phenomenal solution!
We are developing a Continuing Education program at work. Different activities are worth different points. I have created a master data sheet with a form so that individuals can submit their own activities. What I need to do is have the points column autopopulate based on the activity (I do not want individuals adding a point value). A caveat to this is that the activities fall into two different categories, therefore making the points column have to potentially reference two different columns (individually, not combined) to see which of the activity columns has a value in it. I'm assuming I have to start with a table somewhere to identify which activities are worth which point values, but not even sure where to create that. The sheet is shared so you can see the setup. There is no proprietary information on it.
Answers
-
Hi @RSmith30
I hope you're well and safe!
How many different options can there be?
Be safe, and have a fantastic week!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Salutations! Well and safe, thank you. Same to you. :)
There are a total of 15 options. 11 under "application" category and 4 under "training".
Ryan S.
-
@RSmith30 Are you able to provide a screenshot?
-
Additionally, each option in the dropdown menus of the screenshots above has it's own unique point value.
-
I would suggest creating a table that has all options from both columns listed in a single column and the points in another column.
You can then use an INDEX/MATCH like so:
=INDEX({Reference Sheet Points Column}, MATCH(IF(Application@row <> "", Application@row, Training@row), {Reference Sheet Options Column}, 0))
-
Happy to help!
Yes, I agree with Paul, and that would be my recommendation too. If you had a few options, you could use a Workflow instead, but with so many it wouldn't make sense.
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!