Can you trigger an automation or conditional formatting based on grouped cells/same cell value?

SNickNBCUniUSH

Hello,

I'm doing a work project where we're installing 3 devices into all floors of a building. Those devices are represented in columns. Each time a device is installed, the value = "Yes". However, I need to trigger an alert or take an action only when all 3 devices have been installed into every floor of the building. In the column for building number, there are multiple rows for buildings that have multiple floors.

Does anyone know how I can create a formula or trigger acknowledging every row with the same building number has had all 3 devices installed?

Right now, I'm looking at grouping the rows that have the same building number. I'm not sure if that's a good starting point.

Thank you!

sharkasits

@SNickNBCUniUSH I think I understand what you're trying to do. I would use a helper column (checkbox) with the following formula in it and then use that to do any formatting etc. you want to do.

=IF(COUNTIF([building]:[building],[building]@row) - COUNT(COLLECT([building]:[building],[building]:[building],[building]@row, [device 1]:[device 1], "Yes", [device 2]:[device 2], "Yes", [device 3]:[device 3], "Yes")) = 0, 1, 0)

SNickNBCUniUSH

@sharkasits Thank you so much! It's acting like it wants to work! My only question is which part of the formula triggers the check box? I plugged in everything else correctly (I think) but still showing UNPARS.

Thank you!

Paul Newcome

Are you able to paste in the formula that is giving you the error?

sharkasits

@SNickNBCUniUSH This is the formula you can put in the cell/ column with the checkbox in it. Breaking down the formula...

• Get the count of rows where the building is equal to the building on the row:

COUNTIF([building]:[building],[building]@row)

• Count the rows where the building is equal to the building at the row and all three devices are installed:

 COUNT(COLLECT([building]:[building],[building]:[building],[building]@row, [device 1]:[device 1], "Yes", [device 2]:[device 2], "Yes", [device 3]:[device 3], "Yes"))

• If the difference of the above is zero (all rows for the building have all three devices installed) check the box (1 = checked, 0 =unchecked).

IF(<<block 1 above>> - <<block 2 above>> = 0,1,0)

If that's not working for you, can you add a screenshot of what you have?

SNickNBCUniUSH

@sharkasits that isn't working but I may be doing it wrong. Attached is the screenshot.

I had to black out some info for confidentiality purposes, but Column 1 is the Building Number (You'll notice some buildings are listed multiple times), Column 2 is just where I've been trying some formulas, columns 3-5 are where we track devices installed (looking for 3 "Yes"s), and Column 6 is where I'm looking to have a check box once all devices are installed ("Yes" in columns 3-5) in all floors (rows) contained in the building.

For buildings that are listed multiple times in Column 1, I only want to know when all 3 devices are installed for every row with that building number.

Thank you!!

Smartsheet.png

SNickNBCUniUSH

@Paul Newcome here are a few I've tried, aside from sharkasits suggestions. I'm not sure/double checking if I'm using the "@" function correctly.

Under the building column:

=COUNTIF([Building Number]:[Building Number], [Building Number]@row)

Within a blank new column:

=VLOOKUP("Yes", {Sheet 1 - Status Sheet Range 1}, 1, false))

Paul Newcome

Try something like this:

=IF(COUNTIFS([Building Number]:[Building Number], @cell = [Building Number]@row, [Column3]:[Column3], @cell <> "Yes") + COUNTIFS([Building Number]:[Building Number], @cell = [Building Number]@row, [Column4]:[Column4], @cell <> "Yes") + COUNTIFS([Building Number]:[Building Number], @cell = [Building Number]@row, [Column5]:[Column5], @cell <> "Yes") = 0, 1)

This will count up all three of the "Yes" columns for that [Building Number] where the value is not yes, add each of those three counts together, and if that comes out to zero then check the box.

SNickNBCUniUSH

@Paul Newcome Thanks for this formula. It's not working, but I think I may have an idea why: one of the device column titles includes parentheses and a ">". Image edited and attached. Would that be the cause, and is there a way to circumvent the issue?

Thanks!

Smartsheet2.png

Paul Newcome

That shouldn't be a problem. What error are you getting?

SNickNBCUniUSH

@Paul Newcome #UNPARSEABLE

sharkasits

@SNickNBCUniUSH Can you take a screenshot of the formula you're using? You can black out any of the confidential information. Usually when it's unparseable it's a missing "," or something.

sharkasits

I tried this in a test sheet...

Here's the sheet:

Formula in the All Buildings Ready field:

Formula in the Install Ready (same thing except using 1 and 0 for checkbox output):

Check your syntax to make sure you have all the right brackets and commas. That's what usually gets me :)

SNickNBCUniUSH

@sharkasits This one is a real puzzle! So I used your formulas, and then I got "Blocked" and realized it was because there were other formulas in some cells. Then I got "Incorrect Argument Set".

I'm pretty sure syntax is OK because I have the same color coding pattern as you, but I attached a screenshot anyway.

Thanks so much for your help on this it's really appreciated!

Smartsheet3.png

sharkasits

@SNickNBCUniUSH You have one syntax issue.. see my screenshot below. These are the things that usually get me too.

Let me know if that still isn't working for you.

SNickNBCUniUSH

@sharkasits it worked! Thank you! If you have a spare minute. Can you tell me how the formula works in chunks - especially which chunk gathers all of the same building numbers to compare against multiple rows? - I understand the basics: functions, e.g. count/collect/if/and and "yes/no" strings in quotes, but not sure how the full equation puzzles together. Thank you!