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

Data Validation helps us to control how data entered in a cell or cell range. So using data validation, especially using the List from range, we can achieve the above requirement.

I mean we can ensure that a user is only entering data from a given list in Google Sheets and also such entries keep the following requirements.

• The entered values keep their order in the list.
• The values can repeat more than once.

To fulfill the above requirements, we can use the ‘List from range’ (drop-down) data validation feature. How?

In this Google Sheets tutorial, you can learn that awesome trick, step-by-step.

Example to Enter Values from a List as per the Order in the List in Google Sheets

Here is an example of the above typical data validation method.

In Google Sheets, my list is in D2:D11 which contains file names from 1 to 10 (please scroll down and see the first image).

The list can contain any values, the file names are just for example purpose only. It can be product names, book names, country names, item codes, flight numbers, station codes, like any list.

I want to enforce a user/users or restrict myself to only enter the file names in the range A2:A from the list in D2:D11 and that as per the order of the file names in the list in D2:D11.

But do note that the user has the freedom to enter the file names multiple times complying the above condition.

If you see the ‘wrong data entry’ column, you can understand why the data entries are wrong. The “File 4” comes before “File 3” which violates the requirement.

I didn’t think about such a data validation scenario until one of my readers asked about the same under one of my data validation related tutorials here – The Best Data Validation Examples in Google Sheets.

How Is It Possible Using List from Range?

We will use data validation drop-down aka ‘List from range’ to enforce the user to correctly (as required) input values in A2:A.

In that drop-down, in each row, there will only be two values available to select – one is the value from just above the active cell and the other value is the value next to (below) the above value in the list in D2:D11.

For this, we need to use two helper cells that contain two unique formulas. So that we can use that helper cells instead of the original list in D2:D11 to create the drop-down.

That means in three steps, we can enforce a user to enter values from a list as per the order in the list in Google Sheets. Here are those steps under three subtitles.

Formula 1 – Last Value from Column A or the First Value from the List

We are going to use cell F2 and F3 to generate the required two values to create the list from range drop-down. In those two cells, our Formula 1 will go to the cell F2.

Here is that formula.

=ifna(
     indirect(
        ArrayFormula("A"&MATCH(2,1/(A:A<>""),1))
     ),
     D2
)

We need to understand this key formula that helps us to enter values from a list as per their order in the list.

Here the ArrayFormula("A"&MATCH(2,1/(A:A<>""),1)) will return the cell ID of the last non-empty cell (if any) in column A.

Note: Here is the formula explanation – Address of the Last Non-Empty Cell Ignoring Blanks in a Column (works both in Sheets and Excel).

The Indirect will then use that ID to return the value from the last non-empty cell (if any).

If there are no values in column A, then the formula would return #N/A!. In that case, the IFNA would return value from cell D2, that is the first value in the list.

Please take a close look at the below image to understand what happens in cell F2.

Formula 2 – The ‘Next Value’ in the List

The role of this second formula is to return the value next to (below) the first formula result value from the list. Please key this formula in cell F3.

=iferror(
     if(
        counta(A:A)=0,
        "",
        index(D2:D11,
           match(F2,D2:D11,0)+1
        )
     )
)

Do you want an explanation to this formula too?

Here you go!

The IF function will test whether there are values in column A. The expression here in IF is the COUNTA function which is as below.

if(counta(A:A)=0

If the count of values in column A is 0, then the IF would return a blank "", else the IF would execute this part of the formula – Index(D2:D11,match(F2,D2:D11,0)+1).

Understanding the above Index-Match combination is a must now, right?

The INDEX offsets rows in the list in D2:D11 based on the MATCH output number plus 1.

The MATCH returns the relative position of the formula 1 (F2) value in the list. The plus 1 is to get the relative position of the next value to it in the list. As said above the INDEX offsets this number.

Now we have two formulas – one in cell F2 and the other in cell F3.

Time to create a data validation drop-down to enter values from a list as per the order in the list in Google Sheets.

Please once again note that we won’t use the original list to create the drop-down, instead, we will use the range F2:F3.

List from Range to Enter Values from a List as per the Order in the List

Please follow the below final step.

Select A2:A20 or the range in which you want the drop-down to appear.

Then click the menu Data > Data validation and follow the below ‘List from range’ settings.

Now the user can only enter/select data in A2:A20 (or the range in which you have created the drop-down) from a list as per the order in the list in Google Sheets.

That’s all. Enjoy!

Sample_Sheet_11920