Conditional Formatting issue

Jsdeloria21
Frequent 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

Re: Conditional Formatting issue

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

Re: Conditional Formatting issue

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

Re: Conditional Formatting issue

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

Jsdeloria21
Frequent Contributor

Re: Conditional Formatting issue

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

Re: Conditional Formatting issue

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
Frequent Contributor

Re: Conditional Formatting issue

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

Re: Conditional Formatting issue

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
Frequent Contributor

Re: Conditional Formatting issue

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

Re: Conditional Formatting issue

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

Jsdeloria21
Frequent Contributor

Re: Conditional Formatting issue

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,