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

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. 👍️