Auto populating a cell value based on potentially two different columns

RSmith30

@Paul Newcome @James Keuning

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.

https://app.smartsheet.com/sheets/5MrhpwX3rM4ghvXqW8xrXXM6qwwp4QhPgGPW3pm1?view=grid

Andrée Starå

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!

RSmith30

@Andrée Starå

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.

Paul Newcome

@RSmith30 Are you able to provide a screenshot?

RSmith30

RSmith30

@Paul Newcome @Andrée Starå

Additionally, each option in the dropdown menus of the screenshots above has it's own unique point value.

Paul Newcome

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))

Andrée Starå

@RSmith30

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.