Google Sheets Data Validation and Protection 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?

Accepted Answer

Criteria: List from a range, with the range specified as 'Projects'!A2:A10.

Answer

Criteria: List of items, with the project names manually typed in and separated by commas.

Answer

Criteria: Custom formula is, with the formula =VLOOKUP(C1, 'Projects'!A2:A10, 1, FALSE).

Answer

Criteria: Checkbox, with the project names used as labels.

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?

Accepted Answer

=B2>A2

Answer

=$B$2>$A$2

Answer

=ISAFTER(B2, A2)

Answer

=B:B>A:A

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?

Accepted Answer

Use the 'Protect range' feature on A1:D10 and set permissions to 'Only you'.

Answer

Hide rows 1 through 10 from view.

Answer

Use Data Validation with the 'Reject input' option on cells A1:D10.

Answer

Apply conditional formatting to make cells A1:D10 have a red background.

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?

Accepted 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.

Answer

'Reject input' sends an email notification to the sheet owner about the invalid entry, while 'Show warning' does not.

Answer

'Reject input' deletes the row containing the invalid data, while 'Show warning' highlights the cell.

Answer

'Reject input' is for text-based criteria, while 'Show warning' is for number-based criteria.

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?

Accepted Answer

Protecting the sheet and then granting 'Can edit' permissions only to the specific ranges (B2:B10, C2:C10).

Answer

Protecting the range for the entire sheet (A1:Z1000).

Answer

Using a custom data validation formula to check the row count.

Answer

Hiding all unused rows and columns on the sheet.

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?

Accepted Answer

Criteria: Number, between, 1 and 5.

Answer

Criteria: Text, contains, "1,2,3,4,5"

Answer

Criteria: Custom formula is, =AND(F1>=1, F1<=5)

Answer

Criteria: List of items, with "1,2,3,4,5" as the items.