Google Sheets Data Validation and Protection Flashcards

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?

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.

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?

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.

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?

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.

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?

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.

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?

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.

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?

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.