Formula to bring dependent values

Hello Experts,

I need to return a formula with Yes or No based on a sequence of current status and the preceding steps needs to be covered.

The sequence is applicable as below.

Pending Dependencies (F)
Not Started (F)
Pre Set Up (F)
Set Up (F)
Smoke Test (F)
Smoke Test Validation (F)
Full Run (F)
Full Run Validation (F)
Complete (F)

Example -

If 1. Finance task = Pending Dependencies (F), it should return YES in the Pending Dependencies (F) Column.

2nd - If HR is in current Not Started (F) status, formula should return Yes and previous step Pending Dependencies (F) should return Yes and subsequent step should return No.

I need to return the values for the entire 9 status. Also if there is an option to do via lookup ?

Find more posts tagged with

Formulas

Accepted answers

All comments

jmyzk_cloudsmart_jp

Hi @tamiz.vedachalam

In the demo solution below, I added the [Task Number] helper column.

https://app.smartsheet.com/b/publish?EQBCT=ece4b3bbc13b464fb13d7cd77546174e

The column references another sheet that returns the status number for a given status.

[Task Number] =INDEX({Status Number : Number}, MATCH([Current Status]@row, {Status Number : Status}, 0))

https://app.smartsheet.com/b/publish?EQBCT=a4f438153b1c46978f6db410d469ff62

Using the status number, we can determine the Yes/No value for each status column using the following formula.



[Pending Dependencies (F)] =IF([Task Number]@row >= 1, "Yes", "No")

[Not Started (F)] =IF([Task Number]@row >= 2, "Yes", "No")

[Pre Set Up (F)] =IF([Task Number]@row >= 3, "Yes", "No")

[Set Up (F)] =IF([Task Number]@row >= 4, "Yes", "No")

[Smoke Test (F)] =IF([Task Number]@row >= 5, "Yes", "No")

[Smoke Test Validation (F)] =IF([Task Number]@row >= 6, "Yes", "No")

[Full Run (F)] =IF([Task Number]@row >= 7, "Yes", "No")

[Full Run Validation (F)] =IF([Task Number]@row >= 8, "Yes", "No")

[Complete (F)] =IF([Task Number]@row >= 9, "Yes", "No")

I applied conditional formatting for easy reference.

Naeem Ejaz

https://app.smartsheet.com/r/template-gallery/templates/20baa46b-fd68-4ffc-8b64-b666c0819c22/Employee-Pulse-Check?tg-locale=en_US

This Employee Pulse Check template will help your scenario.

nicksnav

Hey @tamiz.vedachalam !

This was a fun ask! I built mine similiar to @jmyzk_cloudsmart_jp with a few differences, but I believe coming to the same place. I built two sheets. Sheet 1 holding all Current Statuses and their corresponding responses and Sheet 2 that will hold Tasks a multiselect column for the [Current Status].

Sheet 1:

Sheet 2:

This sheet holds the Tasks column for you to choose your departments. It has column formulas for each Status that mirror the status scenarios chart built on sheet 1.

Column Formulas:

Pending Dependencies (F):

=INDEX({Pending Dependencies}, MATCH([Current Status]@row , {Current Status D}, 0))

Not Started (F):

=INDEX({Not Started}, MATCH([Current Status]@row , {Current Status D}, 0))

Pre Set Up (F):

=INDEX({Pre Set Up}, MATCH([Current Status]@row , {Current Status D}, 0))

Set Up (F):

=INDEX({Set Up}, MATCH([Current Status]@row , {Current Status D}, 0))

Smoke Test (F):

=INDEX({Smoke Test}, MATCH([Current Status]@row , {Current Status D}, 0))

Smoke Test Validation (F):

=INDEX({Smoke Test V}, MATCH([Current Status]@row , {Current Status D}, 0))

Full Run (F):

=INDEX({Full Run}, MATCH([Current Status]@row , {Current Status D}, 0))

Full Run Validation (F):

=INDEX({Full Run V}, MATCH([Current Status]@row , {Current Status D}, 0))

Complete (F):

=INDEX({Complete}, MATCH([Current Status]@row , {Current Status D}, 0))

Thanks for asking! That one was fun to figure out! However you build it I hope it works well for you! Have a great day! 😁

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.