Need formula to highlight row if same site # was entered within the week

zoe.h

Hi,

My team currently works off a Smartsheet that tracks task e-mails we receive. Recently, we've been receiving a lot of duplicate items in our e-mail that we categorize with Task Subcategory "(TE) Critical Date Entry" on our tracker. Each e-mail corresponds to a specific site with a specific Location #.

Basically, I'd like to create a helper column next to "Location #" that is a checkbox column that would be checked if the same location number, with the Task Subcategory being "(TE) Critical Date Entry", was requested in the last 5 business days (although 7 regular days would suffice). I would then like to create a conditional format that highlights the whole row orange if that box is checked, indicating it's a duplicate entry that needs to be deleted.

Can I get some help with the formula? See attached below of the tracker:

Thank you!

Brian_Richardson

For the checkbox formula you can use:

= IF(COUNT(COLLECT([Task Subcategory]:[Task Subcategory],[Task Subcategory]:[Task Subcategory],"(TE) Critical Date Entry", [Location #]:[Location #], [Location #]@row, [Date Requested]:[Date Requested], >=TODAY(-5)))>1,true)

That should check the box anytime you have more than 1 task with category "(TE) Critical Date Entry" and the same location added in the past 5 days.

Then you can create a conditional format to flag the row with color when the checkbox is checked, as you indicated. Note you don't have to flag the entire row, you can also just flag certain columns in color, or change a card/gantt bar.

zoe.h

Hi Brian!

Thanks for the formula! I entered it into the checkbox column and set as column formula, but it doesn't seem to be checking the box. See the test below:

SSFeatures

Hi @zoe.h

I created a similar sheet as yours and tested out the formula and it worked for me. It worked as a cell formula and a column formula:

One thing that I noticed is that the formula will click the checkbox even for older entries.

Notice that 09/01/24 is checked even though it itself is older than 5 days ago. One way that we can improve upon the formula is by wrapping it in an AND and checking that the specific row is not older than 5 days.

=IF(
	AND(
		COUNT(
			COLLECT(
				[Task Subcategory]:[Task Subcategory],
				[Task Subcategory]:[Task Subcategory],
				"(TE) Critical Date Entry", 
				[Location #]:[Location #], 
				[Location #]@row, 
				[Date Requested]:[Date Requested],
				>=TODAY(-5))
		) > 1,
  	        [Date Requested]@row >= TODAY(-5)),	
	true
)

You might not need this though. You'll only need this if you only want to highlight matching rows that are within the past 5 days.

Best!

zoe.h

Hi again! This is so silly, but I found out what the issue is: our 5-digit Location numbers begin with a 0, but once removed from both numbers, the formula checks the box. We can't remove the 0 from the site numbers. Is there a quick fix for this issue without removing the 0?

zoe.h

Okay! I think I've figured it out by creating a hidden Location # Helper Column with a VALUE formula. Phew.

I also assume I could nest a VALUE function somewhere in that beautiful formula above.

Brian_Richardson

@SSFeatures nust wanted to say I like how you broke out the formula. God I wish we had a code block for entering formulas on sheets instead the long run-on cell.

@zoe.h sounds like you’ve got it! Yes you can wrap VALUE around the column references inside the formula.

SSFeatures

@Brian_Richardson Haha thanks a lot. I know! It would be awesome if we had code blocks and also auto formatting within those blocks. For example if I could press CTRL+Q or something and have it format into a nice format.

I've thought about implementing it the browser extension but I've noticed that the formulas behave weirdly if they have line breaks, specifically with cross-sheet references.

Best!

Nathan Braun

zoe.h

Hi again, everyone! I'm genuinely stumped. See screenshot below.

We began using the tracker with the exact formula provided by @SSFeatures, except I changed the dates from -5 to -14. So the exact formula I'm using is:

=IF(AND(COUNT(COLLECT([Task Subcategory]:[Task Subcategory],[Task Subcategory]:[Task Subcategory],"(TE) Critical Date Entry", [Location #]:[Location #], [Location #]@row, [Date Requested]:[Date Requested],>=TODAY(-14))) > 1,          [Date Requested]@row >= TODAY(-14)),true)

Yet it's not checking the box for "Possible Duplicate CDM?". Does the data I've provided indicate it should?

zoe.h

This is really bizarre. I created a copy of the tracker, deleted all data, and manually input same location number, date requested, and task subcategory, and it's highlighting both lines (although I'd like it to just highlight the new line that was entered). Why would the irrelevant data we're entering on the tracker be impacting the formula?

Brian_Richardson

It's probably the issue with your leading 0s again, try using your Location # Helper column as a replacement for Location # and see if that works.

zoe.h

Apologies - I also did that. So the formula I'm currently using is:

=IF(AND(COUNT(COLLECT([Task Subcategory]:[Task Subcategory], [Task Subcategory]:[Task Subcategory], "(TE) Critical Date Entry", [Location # Helper]:[Location # Helper], [Location # Helper]@row, [Date Requested]:[Date Requested], >=TODAY(-14))) > 1, [Date Requested]@row >= TODAY(-14)), true)

And it's not working on the original sheet but working on a copy of the sheet with all data deleted.

Brian - let me know if you'd like me to grant you access to the sheet if you're interested in looking for yourself.

Brian_Richardson

The formula looks ok so something in the data is throwing things off.

Check the Task Subcategory value for that first example row and make sure there isn't an extra space or something trailing it. Esp if it was entered through an import, sometimes you can get special characters or line feeds etc in there and not realize it…and cause the formula not to match.

Check the Date and make sure it's a real date from the calendar and not some text that got ported in.

If it's still not working then break up the formula to check each piece. So for example just do a

=COUNT(COLLECT([Task Subcategory]:[Task Subcategory], [Task Subcategory]:[Task Subcategory], "(TE) Critical Date Entry") and see how many hits you get. Then add the Location Helper bit. Then add the Date Requested etc until you suddenly get a count of 1 and not 2 or more.