Conditional formatting based on conditions in row/column

[Sohdaw]

Hi,

I have a workflow where im looking at putting conditional formatting on the "Grand Total" and "% of Limit" rows, to highlight cell background as RED

a) any values in the two rows < 0 

b) if the limit in Grand Total is exceeded, eg. x > 100M. 

Note that i have used a union tool to join the rows 1 & 2 and 3 & 4 together.  

	Limit 100M
	Description	Date T-1	Date T	Date T+1	Date T+2	Date T+3
1	abc
2	def
3	Grand total	x	y	etc
4	% of Limit	A	B	etc

[ShankerV]

Hi @Sohdaw 

Yes, it can be achieved with the help of Basic Table tool.

Using the coloring option, Creating a Row rule.

[ShankerV]

Hi @Sohdaw 

Hope you are expecting the result like this.

Hope it helps!!!

Many thanks

Shanker V

[Sarreddy]

@Sohdaw  

Some example attached for conditional formatting.

[Sohdaw]

Thanks @ShankerV , one point to note is that my dates are dynamic fields. 

Date T-1 / Date T / Date T +1 / Date T +2 / Date T+3 

If i create the rule manually for all 5 date columns, will that work? 

Also for Grand Total it needs to flag up in RED if the amount exceeds the Limit. What's the easiest way to make that work? 

[ShankerV]

Hi @Sohdaw 

Breaking down to answer all your questions.

1. Yes, if you create a rule manually for 5 date columns separately. It will work.

I shown an example for 1 row.

2. Also I have shown the sample for <0.

You need to create the same rule for all 5 date columns if >100million.

Hope it helps!!!!

Many thanks

Shanker V

[ShankerV]

Hi @Sohdaw 

Wanted to tweak a better solution for my solution 2 in previous reply.

We can use the same formula to achieve both <0 and >100m.

Here below,

tonumber([Date T-1])<0 or tonumber([Date T-1])>1000

Please add as many zero's for 100m. I just did for testing.

Many thanks

Shanker V

[Sohdaw]

Hi @ShankerV , i created the column rules as per your advice. However only the top row is highlighted in RED background colour, not the Grand Total row.

Any idea why? 

Description	19-Jan-2023	20-Jan-2023	23-Jan-2023	24-Jan-2023	25-Jan-2023
Abc --> Only this row is in RED background	237,947,672	224,933,029	219,567,492	219,567,492	219,567,492
def	531,999	531,999	531,999	531,999	531,999
ghi	0	0	0	0	0
Grand Total	238,479,671	225,465,028	220,099,491	220,099,491	220,099,491
Limit Checking %	119	113	110	110	110

[ShankerV]

Hi @Sohdaw 

Could you please check and advise whether you are looking the results like below.

Many thanks

Shanker V

[Sohdaw]

Ideally for the cells that are in the column which contain the dates, should flag up with background red if

a) their amount exceeds the limit (i have another column which i have hidden) - limit in this table is 200M

b) value < 0 

Also is there any way to flag if cells in the row Limit Checking % > 100?