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,

 

Best Answers

  • LipChean_Soh
    Answer ✓

    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

  • LipChean_Soh
    Answer ✓

    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

     

Answers

  • 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

     

     

  • 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,

  • 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

     

  • 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

     

     

     

  • 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,

  • 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,