.png)
Thefeature in Sheetrocks allows users to define rules and restrictions on specific data ranges to ensure data quality and control what types of data are allowed in a spreadsheet. This is particularly useful in team environments, workflows with multiple contributors, or when automating data processing.
Data constraints are rules applied to specific cells or ranges that control what type of data can be entered. They validate data in real-time, ensuring that only appropriate values are accepted according to the predefined logic.
For example, you can enforce:
Improve Data Quality: Data constraints are an effective way to ensure that your data is consistent and valid. For example, you can prevent users from entering invalid email addresses, non-numeric values in numeric fields, or dates outside a given range.
Restrict Data Entry: Use data constraints to prevent users from entering unwanted or incorrect values. This is useful when managing large datasets where consistency is key for accurate analysis.
Per-User Access: Combine data constraints with Sheetrocks' permissions system to allow users to see and edit only their own data within a shared workbook. This is especially useful for team collaboration or when multiple users are working on the same sheet but need to be restricted to their own entries.
1. Select a Range: Highlight the range where you want to apply the data constraint (e.g., column C where users will input numeric values).
2. Access Data Constraints:Right-click on the selected range and choose the Data Constraints option. Alternatively, access it through the Edit menu.
3. Define the formula: For example, to ensure that only numbers greater than 300 are entered, use the formula:
=AND(ISNUMBER(cell), cell > 300)
4. Save the Constraint: Once saved, any attempt to enter non-numeric data or numbers less than 300 will result in an error, preventing invalid data from being entered.
5. Error Handling: If a user enters invalid data, they will see an error message explaining the violation of the constraint.
Data constraints are not retroactive, meaning they only apply to new data entries after the constraint is set. Data already in the sheet before setting the constraint will remain unchanged, even if it violates the new rule. This approach ensures that existing data isn't unexpectedly altered when constraints are applied.
Sheetrocks data constraints allow a wide variety of validation options. You can create constraints using any formula that evaluates to TRUE or FALSE, such as
1. Number Constraints: Ensure that values are numeric or within a specific range.
Example:
=AND(ISNUMBER(cell), cell > 0)
2. Text Constraints: Validate that the data is text-based or matches a specific format.
Example:
=ISSTRING(cell)
3. Date Constraints: Ensure that the data entered is a date, or limit the date to specific ranges.
Example:
=AND(ISDATE(cell), cell > TODAY())
4. Custom Conditions: Use any complex formula to validate data. For example, ensuring that a value is both a number and greater than 300.
Example:
=AND(ISNUMBER(cell), cell > 300)
When a data constraint is violated, users will see an error message with details about the violation. This message helps users correct their inputs by showing what the expected data type or range should be.
If the formula used for the constraint is invalid, you will receive an error message when attempting to save the constraint, allowing you to debug the formula before applying it.
Named Ranges: Combine named ranges with data constraints for more readable and maintainable formulas. This is particularly useful when creating complex validation rules.Permissions: Data constraints work well with Sheetrocks’ granular permissions model. By combining input-only mode with data constraints, you can confidently share a workbook while ensuring that users can only enter valid data in designated areas.Conditional Formatting: Use conditional formatting to highlight cells that meet certain conditions, making it easy to spot invalid data even before constraints are applied.
.png)