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

Harry N Nomikos 1,266 Reputation points
2023-05-21T08:25:55.0966667+00:00

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

SharePoint Server
SharePoint Server
A family of Microsoft on-premises document management and storage systems.
2,297 questions
SharePoint
SharePoint
A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.
10,262 questions
0 comments No comments
{count} votes

Accepted answer
  1. Ling Zhou_MSFT 15,320 Reputation points Microsoft Vendor
    2023-05-22T02:54:07.9366667+00:00

    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:

    2


    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.

    0 comments No comments

0 additional answers

Sort by: Most helpful