What formula can I use to set a checkbox value based on a row ID above or below a specific row?

BO'Neil

Line in the plan (In scope formula) checkbox use case:

In simple terms I’m looking for a formula if a Row ID is above a given Row, the product task is in scope (Out of Scope checkbox column/row is unchecked) and if the Row ID is below the “Out of Scope Below” row ID, then the out of scope checkbox is checked.

Need:

I have a blueprint project plan that has a list of potential products that can be part of a clients project.

Each product starts it’s project plan life below the out of scope line and the out of scope checkbox checked. Each product has a list of tasks that get moved above the “Out of Scope Below” row ID when a deal is closed with a client and that product is in scope.

I want the out of scope checkbox to automatically be cleared when the items move above the line and I want the out of scope checkbox to be set when the product task is moved below the line.

I’m looking for a formula that is linked to a row ID number, however that row id number will change as product tasks get moved above or below the line.

e.g. the “out of scope below” row ID can start out as Row ID number 100 but it can be changed to row 200 (or any other number) as product tasks get moved above the line.

Any recommendations on how I can use the Row ID value linked to a formula? 

BO'Neil

Heather, Thanks for the recommendations. I'll try out the formulas and get them added to my in-flight projects.

Have a grand day :)

HeatherD.

Hi @BO'Neil !

First you can set up your Row ID with this column formula: =MATCH(Project@row, Project:Project, 0)

Per this community thread: https://community.smartsheet.com/discussion/69476/row-numbers The above formula will maintain the row numbers in order even when you rearrange the rows. Note: This relies on the Project column's cells to have unique values. If you have duplicate cells in the Project column, the row number will likely duplicate the row number of the first instance of that Project. You will definitely want to reference a column that will always have unique values.

Next, in your Out of Scope checkbox column, you can use this column formula: =IF([Row ID]@row < MATCH("Out of Scope Below", Project:Project, 0), 1, 0)

The checkbox formula looks at the Row ID number and compares it to the row ID of the "Out of Scope Below" row. If the current row ID is less than the row ID of the Out of Scope Below row, then it will check the box; otherwise, the box will be unchecked.

Best,

Heather

BO'Neil

Heather, Thanks for the recommendations. I'll try out the formulas and get them added to my in-flight projects.

Have a grand day :)