How do I create and If Formula pulling from another sheet to designate indicators?

Good Morning All,

I am working on a Overall Health for a Project that looks like this:

I need to update the lights by using an if statement looking at three different columns of another sheet that are not next to one another on the sheet, example of that sheet below

In the first sheet for each Column I need to look at the three rows Criteria Most Impacted, Due Date and Done to determine the color of the light, Green, Yellow, Red.

Lets use Infrastructure as an example

on the first sheet referencing the second sheet the code would need to do the following:

If the Criteria Most Impacted is equal to Infrastructure and the Due Date is greater than 5 Days and the Done Column is not checked, the indicator should be Green.

If the Criteria Most Impacted is equal to Infrastructure and the Due Date is between 1 to 4 Days out and the Done Column is not checked, the indicator should be Yellow.

If the Criteria Most Impacted is equal to Infrastructure and the Due Date Equal to or Less than day or is overdue and the Done Column is not checked, the indicator should be Red.

I am more of a DB analytics person so the calculation is done in SQL or ABAP code, I am a bit lost in the Smartsheet code.

HELP!

Caroline

Find more posts tagged with

Accepted answers

Paul Newcome

If it isn't weekly, then you would use a COUNTIFS in nested IFs.

=IF(COUNTIFS({Criteria}, @cell = "Infrastructure", {Due Date}, @cell<= TODAY())> 0, "Red", IF(.....

You would continue that out and adjust the number of days in the TODAY() function to determine your RYG.

Paul Newcome

{Done}, @cell = 1

is looking for rows where the box IS checked. We want to look for rows where the box IS NOT checked.

{Done}, @cell <> 1

All comments

Paul Newcome

You are missing the very first IF( at the beginning, all of your COUNTIF functions should be COUNTIFS functions, and you only need to enter the {Range}. If you click on the column header, the {Range} will automatically cover the entire column. You do not need to use {Range}:{Range}.

You are actually very very close. You've got this.

=IF(COUNTIFS(..................), "Red", IF(COUNTIFS(................), "Yellow", "Green"))

{Due Date}~~:{Due Date}~~

delaurellc

Hi Paul,

Now I am getting invalid operation error :o(

=IF(COUNTIFS({RADIO Log Range 2}, {Criteria Most Impacted}, @cell= "Infrastructure", {Due Date}, @cell<= TODAY(), {Done}, @cell= 1> 0), "Red", IF(COUNTIFS({RADIO Log Range 2}, {Criteria Most Impacted}, @cell= "Infrastructure", {Due Date},@cell>= TODAY(2), "Yellow", "Green"))

I have tried multiple iterations of this and no joy. Am I using the correct brackets? In all the code I see for smartsheets the brackets are [] not {}

Caroline

Genevieve P.

Hi @delaurellc

It looks like you are missing some closing parentheses, which is what's giving you the current error.

To clarify, {these} are cross sheet references (see here) whereas column references [in these] are looking at columns in the same sheet (see here).

Try this structure:

=IF(COUNTIFS({RADIO Log Range 2}, {Criteria Most Impacted}, @cell= "Infrastructure", {Due Date}, @cell<= TODAY(), {Done}, @cell= 1) > 0, "Red", IF(COUNTIFS({RADIO Log Range 2}, {Criteria Most Impacted}, @cell= "Infrastructure", {Due Date},@cell>= TODAY(2)) > 0, "Yellow", "Green"))

Cheers,

Genevieve

Paul Newcome

You will also need to get rid of {RADIO Log Range 2} from both COUNTIFS.

You have

range / range / criteria / range / criteria

it should be

range / criteria / range / criteria

Side note: I don't see the checkbox range/criteria set included in the second COUNTIFS.

Genevieve P.

Good catch @Paul Newcome 😎

delaurellc

Paul and Genevieve,

so I am not sure what other range I have referenced? The Criteria not Met is a column on the RADIO log Sheet so what other reference do I have to show the formula where the data to Count is?

I have removed the reference to the RADIO log and now getting #INVALID REF. This is what I have:

=IF(COUNTIFS({Criteria Most Impacted}, @cell= "Infrastructure", {Due Date}, @cell<= TODAY(), {Done}, @cell= 1) > 0, "Red", IF(COUNTIFS({Criteria Most Impacted}, @cell= "Infrastructure", {Due Date},@cell>= TODAY(2)) > 0, "Yellow", "Green"))

Genevieve thank you for sending all the material, I already have the formula item downloaded and reading the other item I meet all the criteria to reference another sheet. Just goes to show you can be a data engineer on a database and something simple like excel or smartsheet can trip you up. I am feeling really frustrated at this point that I cannot figure this out.

You guys are great, I am just missing some brain cells lately.

Caroline

Genevieve P.

Hey @delaurellc

No worries - it happens to all of us and formulas can be tricky!

It sounds like each of your {references} may not have been created correctly, going to the right column in the other sheet.

Here's a recent thread where we went through how to create a {cross sheet reference}

https://community.smartsheet.com/discussion/comment/380076#Comment_380076

All of your references will need to be created in this way:

{Criteria Most Impacted}`

{Due Date}

{Done}

Does that help?

Cheers,

Genevieve

delaurellc

I am now getting an indicator but it is the wrong one :o( It is giving me the green but my the record I have in the Radio log the indicator should be showing as red because it is in the past and not done.

=IF(COUNTIFS({Criteria Most Impacted}, @cell = "Infrastructure", {Due Date}, @cell <= TODAY(), {Done}, @cell = 1) > 0, "Red", IF(COUNTIFS({Criteria Most Impacted}, @cell = "Infrastructure", {Due Date}, @cell >= TODAY(2), {Done}, @cell = 1) > 0, "Yellow", "Green"))

here is the record I am looking at right now the criteria most wanted is correct the Due date is less than today and the done cell is empty so it should show as red

It is green

something I missed? Should I try the HAS function in the last thread? and if I do HAS(@cell where does the close ) go?

Paul Newcome

{Done}, @cell = 1

is looking for rows where the box IS checked. We want to look for rows where the box IS NOT checked.

{Done}, @cell <> 1

delaurellc

This worked, thank you for hanging in there with me. I appreciate you so much!

Caroline

Paul Newcome

Happy to help. 👍️

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.