Conditional Formatting issue

Jsdeloria21
Regular Contributor

Conditional Formatting issue

Hi,

Can I ask for help on the conditional formatting part. I have an input template where once the user inputted more than a hundred percent the cells should turn red stating that this is the maximum percentage they can enter. I created a rule (see below) but it seems to be not working.See below

 

Both row and column are dimensions and Share% is a line item.

Jsdeloria21_0-1624935639962.png

conditional formatting Rule

 

Jsdeloria21_1-1624935678138.png

Am I doing it wrong? Anyone who can correct me on this I will gladly appreciate the help

 

Regards,

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
LipChean_Soh
Certified Master Anaplanner

Hi @Jsdeloria21 

 

You might want to try changing the numbers to:

Min: -0.01

Mid: 0.5

Max: 1.01

 

Also, you should also turn off the summary, because a percentage larger than 100% for parents isn't meaningful.

 

Thanks,

LipChean

View solution in original post

LipChean_Soh
Certified Master Anaplanner

Hi @Jsdeloria21 

 

1.1. Value line item - is where you set the conditional formatting, correct? Value line item is where you enter the % percentage.

 

2. %CF - is used to check if the total at parent level is 100%,right? Yes, and whether there's any input in 'Value', it doesn't make sense to set a blank cell to red, even if the parent is larger than 100%, right?

 

3. the screen shot on number 2 - is this the part where you tell the user that it exceeded 100% already? 

 

Yes, and I created a few more examples below.

LipChean_Soh_0-1624947723732.png

 

Thanks,

LipChean

 

View solution in original post

8 REPLIES 8
LipChean_Soh
Certified Master Anaplanner

Hi @Jsdeloria21 

 

You might want to try changing the numbers to:

Min: -0.01

Mid: 0.5

Max: 1.01

 

Also, you should also turn off the summary, because a percentage larger than 100% for parents isn't meaningful.

 

Thanks,

LipChean

Jsdeloria21
Regular Contributor

Hi LipChean,

First of all thank you for taking time in responding to my concern. However, when I entered 100% to the next cell it turns to white. How can I make it turn red. see below

 

to alert the user that they cannot enter 100% anymore.

Jsdeloria21_0-1624945636458.png

Regards,

Akhtar.shahbaz
Community Boss

Hi @Jsdeloria21 

 

Add one line item for the conditional formatting where you have to write the rule for share % & used this CF line in your conditional formatting.

 

Thanks

Akhtar

 

 

Jsdeloria21
Regular Contributor

Hi Akhtar,

 

do you mean like this. see below. another line item and then in the conditional formatting i will use this line item instead of share%?

 

Jsdeloria21_0-1624945900334.png

 

LipChean_Soh
Certified Master Anaplanner

Hi @Jsdeloria21 

 

1. Assuming 'Region L1' is the parent of 'Country L2'.

LipChean_Soh_0-1624946229907.png.        LipChean_Soh_2-1624946250571.png

2. Assuming 'Value' is where you enter the %.

LipChean_Soh_3-1624946313871.png

 

3. '% CF' is used to check whether the total % at parent level is more than 100%. Note that i have added another test to ensure only cells with values are highlighted in red, i.e. blank cells do become red.

LipChean_Soh_4-1624946329130.png

 

4. A System module is created to follow DISCO.

LipChean_Soh_5-1624946339233.png

 

Thanks,

LipChean

 

 

 

Jsdeloria21
Regular Contributor

My apologies just a couple of questions more.

1. Value line item - is where you set the conditional formatting, correct?

2.%CF - is used to check if the total at parent level is 100%,right?

3. the screen shot on number 2 - is this the part where you tell the user that it exceeded 100% already?

 

Regards,

LipChean_Soh
Certified Master Anaplanner

Hi @Jsdeloria21 

 

1.1. Value line item - is where you set the conditional formatting, correct? Value line item is where you enter the % percentage.

 

2. %CF - is used to check if the total at parent level is 100%,right? Yes, and whether there's any input in 'Value', it doesn't make sense to set a blank cell to red, even if the parent is larger than 100%, right?

 

3. the screen shot on number 2 - is this the part where you tell the user that it exceeded 100% already? 

 

Yes, and I created a few more examples below.

LipChean_Soh_0-1624947723732.png

 

Thanks,

LipChean

 

Jsdeloria21
Regular Contributor

Hi LipChean,

So I will show you what I have so far just so I can capture what you are trying to say.

 

So this is what the template looks like.

Jsdeloria21_0-1624948172855.png

This is the blue print view

Is this where I will create the condition IF Share % <>0 then 1 else blank?

Jsdeloria21_1-1624948206825.png

and this is the rule in my conditional formatting. i am assuming this is alright for now?

Jsdeloria21_2-1624948288331.png

 

So how do I call the conditional formatting rule in the line item Share % to take effect in my template as a validation if it reaches 100%?

 

My apologies if I have too many questions.I just want to clearly grasp the concept you are trying to send me.

 

Regards,