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!
Answers
-
@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)
-
@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!
-
Are you able to paste in the formula that is giving you the error?
-
@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?
-
@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!!
-
@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))
-
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.
-
@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!
-
That shouldn't be a problem. What error are you getting?
-
@Paul Newcome #UNPARSEABLE
-
@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.
-
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 :)
-
@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!
-
@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.
-
@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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 405 Global Discussions
- 216 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!