Formatting cell color based on compairing rows and column data


I have a table, where I have column 1 as Sub categories for tickets, with the department that should handle it in ()'s. Row1 columns 2+ are the names of the departments, and the cross section are the count of which department completed the ticket.

Is there a way to format the cells when the department that has a count, is not the named department in column 1? Would this require regex?


Please post at least a bogus table with fake data and not blurred

Category dept1 dept2 dept3
Account/Creation (dept1) 6 0 0
Account/Deletion (dept1) 1 3 0

In this example, I would need the square of 3 in row 3 to be highlighted, because dept 2 worked a ticket that should have stayed with dept1.

I do not have the ability to create dummy data in our databases, the best I could do would be taking a screenshot of excel of the above table.

1 Like

Understood, but in order to help you we have to have dummy data like you provided. :call_me_hand:it helps us help you

Do your categories actually have the department embedded in them?

Account/Creation (dept1)

Yes, that is correct.

All of our sub categories have the department name in ()'s

first/second (department)
Account/Create (dept2)
Login/Password (dept1) ect ect

It is like that in the database itself?

The database pulls it from our ticketing system, so yes. It is translated, there is a table of category id’s and names associated with the id’s, but it is in that format.

1 Like

The plot gets thicker. This is a bad design that will require hacking to get what you want. This will require you to provide us details of the table(s) schema with data types etc. Cant work with this. How will you connect it back to dept when it is in parenthesis. Unless I am missing something


(select Name FROM Subcategories WHERE SubCategoryId = Tickets.SubCategoryId) AS SubCategory

, SUM(CASE WHEN WorkedBy = ‘Dept1’ Then 1 ELSE 0 END) AS Dept1

, SUM(CASE WHEN WorkedBy = ‘Dept2’ Then 1 ELSE 0 END) AS Dept2

, SUM(CASE WHEN WorkedBy = ‘Dept3’ Then 1 ELSE 0 END) AS Dept3

, SUM(CASE WHEN WorkedBy = ‘Dept4’ Then 1 ELSE 0 END) AS Dept4

, SUM(CASE WHEN WorkedBy = ‘User’ Then 1 ELSE 0 END) AS User

From table
Where $__timeFilter(created)

This is what I’ve got, redacted of course.

I know I can do a regex on Row 1 (SubCategory, Dept1, Dept2, Dept3, Dept4, User). But I am unsure of how to preform a regex on the value in column 1 (subcategory names) and compare that against if there is data in a certian column.

you need to seriously reconsider this design pattern of category name (departmentname) thing.

It is going to make what you want to do impossible and a hack at best. Are you open to a redesign? Otherwise sorry but I cannot help you myself with this design approach. I am getting rashes and hives :wink: