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

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!

Find more posts tagged with

Accepted answers

All comments

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!

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.