SharePoint List Calculated column to return values across 5 separate Readiness activity fields

Question

Hi Team

I require a formula that will return a value of "Active Readiness Activity" when any of the 5 Readiness activity fields equal any off the below options. If the field is left blank, I want it to return "No Active Readiness Activity"

Drop down options
Comms-ITK
Comms LIBP
Comms other (workplace, intranet)
Comms Customer (Mailouts)
Customer- Other (e.g. call campaign)
Embedding
Facilitated Training (zoom, F2F)
Hypercare
Leaders Briefing
Leader Led Training
Monthly Leaders Snapshot
People hub Training
Pilot
Self Directed Online Learning

I've provided a table to show my expected result and an example of how the data will show up

Project 1	Readiness Activity 1	Readiness Activity 2	Readiness Activity 3	Readiness Activity 4	Readiness Activity 5		Expected Results
Project 1	Leaders Briefing	Self Directed Online Learning	Pilot	Comms-ITK			Active Readiness Activity
Project 2							No Active Readiness Activity
Project 3	Comms-ITK						Active Readiness Activity
Project 4	Embedding	Leader Led Training	Self Directed Online Learning	Comms LIBP			Active Readiness Activity
Project 5	Leader Led Training	People hub Training					Active Readiness Activity

Regards,
Harry

Answer 1

Hi Harry N Nomikos,

Thank for posting in the community.

Please enter the following in the Formula of the choice column:

IF(OR(OR([Readiness Activity 1]="Comms-ITK",
      [Readiness Activity 1]="Comms LIBP",
      [Readiness Activity 1]="Comms other (workplace, intranet)",
      [Readiness Activity 1]="Comms Customer (Mailouts)",
      [Readiness Activity 1]="Customer- Other (e.g. call campaign)",
      [Readiness Activity 1]="Embedding",
      [Readiness Activity 1]="Facilitated Training (zoom, F2F)",
      [Readiness Activity 1]="Hypercare",
      [Readiness Activity 1]="Leaders Briefing",
      [Readiness Activity 1]="Leader Led Training",
      [Readiness Activity 1]="Monthly Leaders Snapshot",
      [Readiness Activity 1]="People hub Training",
      [Readiness Activity 1]="Pilot",
      [Readiness Activity 1]="Self Directed Online Learning"),OR([Readiness Activity 2]="Comms-ITK",
      [Readiness Activity 2]="Comms LIBP",
      [Readiness Activity 2]="Comms other (workplace, intranet)",
      [Readiness Activity 2]="Comms Customer (Mailouts)",
      [Readiness Activity 2]="Customer- Other (e.g. call campaign)",
      [Readiness Activity 2]="Embedding",
      [Readiness Activity 2]="Facilitated Training (zoom, F2F)",
      [Readiness Activity 2]="Hypercare",
      [Readiness Activity 2]="Leaders Briefing",
      [Readiness Activity 2]="Leader Led Training",
      [Readiness Activity 2]="Monthly Leaders Snapshot",
      [Readiness Activity 2]="People hub Training",
      [Readiness Activity 2]="Pilot",
      [Readiness Activity 2]="Self Directed Online Learning"),OR([Readiness Activity 3]="Comms-ITK",
      [Readiness Activity 3]="Comms LIBP",
      [Readiness Activity 3]="Comms other (workplace, intranet)",
      [Readiness Activity 3]="Comms Customer (Mailouts)",
      [Readiness Activity 3]="Customer- Other (e.g. call campaign)",
      [Readiness Activity 3]="Embedding",
      [Readiness Activity 3]="Facilitated Training (zoom, F2F)",
      [Readiness Activity 3]="Hypercare",
      [Readiness Activity 3]="Leaders Briefing",
      [Readiness Activity 3]="Leader Led Training",
      [Readiness Activity 3]="Monthly Leaders Snapshot",
      [Readiness Activity 3]="People hub Training",
      [Readiness Activity 3]="Pilot",
      [Readiness Activity 3]="Self Directed Online Learning"),OR([Readiness Activity 4]="Comms-ITK",
      [Readiness Activity 4]="Comms LIBP",
      [Readiness Activity 4]="Comms other (workplace, intranet)",
      [Readiness Activity 4]="Comms Customer (Mailouts)",
      [Readiness Activity 4]="Customer- Other (e.g. call campaign)",
      [Readiness Activity 4]="Embedding",
      [Readiness Activity 4]="Facilitated Training (zoom, F2F)",
      [Readiness Activity 4]="Hypercare",
      [Readiness Activity 4]="Leaders Briefing",
      [Readiness Activity 4]="Leader Led Training",
      [Readiness Activity 4]="Monthly Leaders Snapshot",
      [Readiness Activity 4]="People hub Training",
      [Readiness Activity 4]="Pilot"),OR([Readiness Activity 5]="Comms-ITK",
      [Readiness Activity 5]="Comms LIBP",
      [Readiness Activity 5]="Comms other (workplace, intranet)",
      [Readiness Activity 5]="Comms Customer (Mailouts)",
      [Readiness Activity 5]="Customer- Other (e.g. call campaign)",
      [Readiness Activity 5]="Embedding",
      [Readiness Activity 5]="Facilitated Training (zoom, F2F)",
      [Readiness Activity 5]="Hypercare",
      [Readiness Activity 5]="Leaders Briefing",
      [Readiness Activity 5]="Leader Led Training",
      [Readiness Activity 5]="Monthly Leaders Snapshot",
      [Readiness Activity 5]="People hub Training",
      [Readiness Activity 5]="Pilot",
      [Readiness Activity 5]="Self Directed Online Learning")),"Active Readiness Activity","No Active Readiness Activity")

This is the result of my test:

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.