Google Sheets Data Validation and Protection Questions and Answers 1 — Questions and Answers
Question 1: A project manager has a list of approved project names in cells A2:A10 on a sheet named 'Projects'. On another sheet, in column C, they want users to assign a project to each task. To ensure users can only select from the official list, which data validation setup should be used for column C?
- Criteria: List of items, with the project names manually typed in and separated by commas.
- Criteria: List from a range, with the range specified as 'Projects'!A2:A10. (Correct answer)
- Criteria: Custom formula is, with the formula =VLOOKUP(C1, 'Projects'!A2:A10, 1, FALSE).
- Criteria: Checkbox, with the project names used as labels.
Correct answer: Criteria: List from a range, with the range specified as 'Projects'!A2:A10.
The 'List from a range' criterion dynamically creates a dropdown menu in the validated cells based on the values in the specified source range. This is the correct and most maintainable method, as any updates to the project list in 'Projects'!A2:A10 will automatically reflect in the dropdown.
Question 2: You are creating a timesheet where an 'End Time' in column B must always be later than the 'Start Time' in column A. Which custom formula should be applied as a data validation rule to the range B2:B100 to enforce this logic?
- =$B$2>$A$2
- =B2>A2 (Correct answer)
- =ISAFTER(B2, A2)
- =B:B>A:A
Correct answer: =B2>A2
When applying a custom formula to a range in data validation, the formula should be written as if it were for the first cell in the range (B2). Google Sheets will automatically adjust the relative references for the other cells (B3>A3, B4>A4, etc.). Using absolute references ($B$2>$A$2) would incorrectly compare every cell in the range to only A2.
Question 3: A team lead has a dashboard sheet with complex formulas in A1:D10 that should never be altered. They want collaborators to be able to edit data in the rest of the sheet (from row 11 downwards) but prevent any changes to the formula area. What is the most appropriate action to take?
- Hide rows 1 through 10 from view.
- Use Data Validation with the 'Reject input' option on cells A1:D10.
- Use the 'Protect range' feature on A1:D10 and set permissions to 'Only you'. (Correct answer)
- Apply conditional formatting to make cells A1:D10 have a red background.
Correct answer: Use the 'Protect range' feature on A1:D10 and set permissions to 'Only you'.
The 'Protect range' feature is designed specifically for this purpose. It allows you to lock a specific set of cells, preventing edits by specified users (or everyone except you), while leaving the rest of the sheet open for collaboration. This directly protects the integrity of the formulas.
Question 4: When setting up a data validation rule, what is the key difference between selecting 'Reject input' and 'Show warning' under the 'On invalid data' option?
- 'Reject input' prevents the user from entering the invalid data, while 'Show warning' allows the entry but flags the cell with a red triangle. (Correct answer)
- 'Reject input' sends an email notification to the sheet owner about the invalid entry, while 'Show warning' does not.
- 'Reject input' deletes the row containing the invalid data, while 'Show warning' highlights the cell.
- 'Reject input' is for text-based criteria, while 'Show warning' is for number-based criteria.
Correct answer: 'Reject input' prevents the user from entering the invalid data, while 'Show warning' allows the entry but flags the cell with a red triangle.
'Reject input' provides strict enforcement by displaying an error and preventing the invalid entry from being committed to the cell. 'Show warning' is a softer enforcement that allows the data to be entered but marks it visually with a red triangle in the corner, indicating that it violates the rule.
Question 5: A manager created a budget template for their team. They want to allow team members to input numbers into specific cells (e.g., B2:B10, C2:C10) but want to prevent them from deleting or adding any rows or columns, which could break the template's structure. Which protection method achieves this?
- Protecting the range for the entire sheet (A1:Z1000).
- Using a custom data validation formula to check the row count.
- Protecting the sheet and then granting 'Can edit' permissions only to the specific ranges (B2:B10, C2:C10). (Correct answer)
- Hiding all unused rows and columns on the sheet.
Correct answer: Protecting the sheet and then granting 'Can edit' permissions only to the specific ranges (B2:B10, C2:C10).
The 'Protect sheet' feature allows you to lock the entire sheet's structure and content. Within its options, you can specify certain cells or ranges that designated users are still allowed to edit. This combination perfectly achieves the goal of allowing data entry in specific places while locking the overall layout, including the ability to add/delete rows and columns.
Question 6: To ensure that users can only enter a customer satisfaction score from 1 to 5 (inclusive) in column F, which is the most direct data validation rule to apply?
- Criteria: Text, contains, "1,2,3,4,5"
- Criteria: Custom formula is, =AND(F1>=1, F1<=5)
- Criteria: List of items, with "1,2,3,4,5" as the items.
- Criteria: Number, between, 1 and 5. (Correct answer)
Correct answer: Criteria: Number, between, 1 and 5.
While a custom formula or a list of items could work, the most direct and appropriate method for a numeric range is using the built-in 'Number' criterion. Selecting 'between' and specifying the values 1 and 5 is the intended and simplest way to enforce this rule.
