How can we enforce that a user must enter values from a list in the same order as the list in Google Sheets?

With Data Validation in Google Sheets, we can control how data is entered in a cell or range. By using a data validation drop-down from a range, we can ensure entries follow the order of the original list while still allowing repeats.

In other words, you can make sure a user only enters data from a given list — and that each entry follows these rules:

1. The entered values appear in the same order as the list.
2. Values may repeat.

In this Google Sheets tutorial, you’ll learn step-by-step how to enter values from a list in order using a simple but effective trick.

Example: Enforce Entry Order from a List in Google Sheets

Here’s an example list (in D2:D11) containing file names from File 1 to File 10:

File 1
File 2
File 3
File 4
File 5
File 6
File 7
File 8
File 9
File 10

The list can contain any values — file names are just an example. It could be product names, book titles, item codes, station names, flight numbers, or any ordered list.

Our goal is to restrict entries in column A so that:

• Values must come from the list in D2:D11.
• The order of entries in column A matches the list order.
• Values can repeat.

For instance, if File 3 has already been entered, the next valid entry must be either File 3 (repeat) or File 4 (next in sequence). If File 4 appears before File 3, it’s invalid.

The Logic Behind the Method

We’ll use a data validation drop-down that updates dynamically so that each row only offers two valid options:

1. The same value as the row above (for repeats).
2. The next value in the source list.

To achieve this, we’ll use two helper cells containing formulas that return the “current” value and the “next” value. The drop-down will reference these helpers instead of the full list.

Step 1: Formula 1 — Last Value from Column A or First Value from the List

Enter this formula in cell F2:

=IFERROR(CHOOSEROWS(TOCOL(UNIQUE(A2:A), 3), -1), D2)

Explanation:

• UNIQUE(A2:A) – Removes duplicates from column A.
• TOCOL(..., 3) – Flattens the list and removes blank cells.
• CHOOSEROWS(..., -1) – Returns the last value from the cleaned list.

If column A is empty, it will return the first value from your source list.

Step 2: Formula 2 — Next Value in the List

In cell F3, enter:

=OFFSET(XLOOKUP(F2, D2:D11, D2:D11), 1, 0)

Explanation:

• XLOOKUP(F2, D2:D11, D2:D11) – Finds the current value (F2) in the list.
• OFFSET(..., 1, 0) – Returns the value one row below (the “next” value).

Step 3: Create the Drop-Down List

1. Select the range where you want to enforce the rule (e.g., A2:A20).
2. Go to Insert > Drop-down.
3. Under Criteria, choose Drop-down (from a range).
4. Enter the range F2:F3 (the two helper cells).
5. Click Done.

Now, any user entering data in column A can only select the same value as above or the next value in the original list, ensuring the correct sequence.

Sample Sheet

Related Resources

• Distinct Values in Drop-Down List in Google Sheets
• Google Sheets: Add an ‘All’ Option to a Drop-down from Range
• Create a Drop-Down Menu From Multiple Ranges in Google Sheets
• Highlight Invalid Entries in Drop-down Lists in Google Sheets
• How to Use Relative Reference in Drop-Down List in Google Sheets