[Solved] Min/Max data validation for cells (0 to 100)

[[AS]_Princess]

Is there a way to have a double input validation for the same column?

For example, I want to check that the numbers entered are between 0 and 100. No lower than 0, no higher than 100.
I am able to validate only one of those options. How can I check both at the same time?

[Hagar Delest]

Hi and welcome to the forum!

I've moved your post from Beginner to Calc. Please tell if you were talking about Writer (for a form).

[Bill]

One of the options is "between".

[Lupp]

Any validity setting is per cell, not per column. Concerning Copy/Paste (including fill operations) and CloneFormatting it behaves like a formatting attribute though it cannot be part of the settings for a named cell style.

Anyway: Yo can apply different validity setting to different ranges even of the same column. Two different validity settings for the same cell (or range) don't make sense, imo. If you were thinking of two conditions combined with AND/OR, this isn't possible with 'Validity'. If you had in mind something different, please explain in detail.

[JohnSUN-Pensioner]

If for your purposes it is enough to warn the user "You are mistaken, correct the value", then you can use the capabilities of Conditional Formatting. Create some kind of vibrant cell style and apply it to cells that will not match the given conditions.

ConditionalFormatting2Cond.png

In case you want to provide tighter control over the input values, it makes sense to consider the option of creating an input form for each row - here you will get the opportunity to implement a variety of scenarios for validating input values.

[eeigor]

Conditionals
Custom number formats also up to two conditions, which are written in square brackets like [>100] or [<=100].
When you use conditionals in custom number formats, you override the standard [positive];[negative];[zero];[text] structure.
For example, to display values below 100 in red, you can use:
[Red][<100]0;0
To display values greater than or equal to 100 in blue, you can extend the format like this:
[Red][<100]0;[Blue][>=100]0

Use data Validation to prevent incorrect data entry.

Format Code:
[RED][<0]0;[RED][>100]0;Standard
NOTE: ";Standard" is added automatically

UPD:
Where is it written, you ask? Nowhere. Spied on in Excel, secretly embedded in Calc.

[Bill]

Where is it written, you ask? Nowhere.

It is documented in Help under "Number Format Codes".

According to Help (both AOO and LO), the first section of the code applies to positive values, the second section applies to negative values and the third section applies to 0, In the code as written the first section applies to negative values and the second section applies to positive values greater than 100. This works for some reason in LO, but it does not work in AOO. In AOO the code has to be changed to:

[RED][>100]0;[RED][<0]0;General

After some more testing, the original code does work in AOO. It looks like putting conditions in the sections overrides the requirement that the first section applies to positive values and the second section applies to negative values.

In English versions of LO and AOO, "Standard" in the code must be replaced by "General". The use of "Standard" was what caused the original failure of the code in AOO.

[MrProgrammer]

Hi, and welcome to the forum.

I want to check that the numbers entered are between 0 and 100. No lower than 0, no higher than 100.

Data → Validity → Criteria → Allow → Decimal → Data → Between → Minimum → 0 → Maximum → 100 «or»
Data → Validity → Criteria → Allow → Whole Numbers → Data → Between → Minimum → 0 → Maximum → 100
This image is to clarify Biil's answer yesterday. Decimal allows values like 23.1; Whole Numbers only allows integers.

Is there a way to have a double input validation for the same column?

Data validation is cell property, not a column property, however you can select a range of cells in a column and apply data validity to all of them at once.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know