I have an input module where I need to enter value (%) and it should sum up to 100%
Answers
-
I would suggest using conditional formatting to show different colors when total < 100%, total = 100%, and total > 100%. If you use DCA and not allow users strictly not enter anything once total = 100%, then user won't have an option to update the values later. Up to you, conditional formatting or DCA>
0 -
Dynamic Cell Access is the best option to restrict the cell data entry. So in your scenario once if total becomes 100% restrict the data entry by DCA and to highlight it use conditional format as mentioned above.
0 -
Hello ,
You can use DCA to restrict the write for the line item and base it on boolean that if value=100% then write is false else true
0 -
Hi @Soumyajit
Please use the following link:
You can use DCA (Dynamic cell access) to achieve this.
If you want it to be exact 100% try doing an error message approach just beside the cell telling a person to enter the right values.
But this should help you achieve what you want.
Hope this helps.0 -
Thanks for your response @Anurag0911 and @Kanishq17 , however in DCA if we reach 100% it will disable the cell to enter any more value. Since I am adding random value I may have to change the input for any particular product. It wont allow me to edit that cell anymore after reaching 100%.
0 -
The limitation of DCA is that once the % reaches 100%, as @Dikshant mentioned, is that the users won't have the ability to update values after the condition is met. For this reason, I would suggest using conditional formatting. For example:
Line 1: 20%Line 2: 80%
Line 3: 15%
Line 4 (sum): 115%
You could use CF to mark Line 4 bright red (or whatever color) if the value is over 100%.
As an additional measure, you could also add a text line titled something like:
Over 100%?With the formula being something like:
IF Line 1 + Line 2 + Line 3 > 100%
THEN "Yes, " & TEXT (Line 4 - 100%) & " over"
ELSE "No"
So that the difference is a lot more obvious. In the above case, the line would read, "Yes, 15% Over" (though you might have to play around with how it shows… I wrote that formula suggestion based on memory 😄).
Not necessary but could be helpful. @Soumyajit
0 -
Make a line item that keeps it at 100% by subtracting the extra percentage off (115 - 100 = 15
[15.1 to keep it at 99.9%]← subtract this from the values or last value entered to keep the DCA off and use the message to give an idea what can be entered ) and might need to think how you want to subtract it from the input lines but would it it helpful to add this formulaic line to control the inputs? Just a thought might need more information on what the limitations are here. Hope this helps! @Soumyajit0
