Have you ever wondered how you can enforce users to enter values from a list in a specific order in Google Sheets? Well, worry no more! Data Validation is here to save the day. With the “List from range” feature, you can easily control the data entered in a cell or cell range, ensuring that the values maintain their order in the list. Let’s dive into this awesome trick step-by-step!
Example: Enter Values from a List as per the Order in Google Sheets
Imagine you have a list of file names in column D, ranging from D2 to D11. You want to restrict users, including yourself, to only enter those file names in column A as per their order in the list. It’s important to note that users have the freedom to enter file names multiple times, as long as they follow the order.
In the image above, you can see the “wrong data entry” column, where the data entries violate the required order. But fear not, my friend, there’s an elegant solution to this problem!
How Is It Possible Using List from Range?
To achieve this, we will use the “List from range” data validation feature. In each row, the drop-down will show two values: the value from the cell just above the active cell and the value below it in the list. This way, users can only choose the next value in the list, maintaining the required order.
To implement this, we need two helper cells, F2 and F3, with unique formulas. These formulas will generate the necessary values for the drop-down.
Formula 1 – Last Value from Column A or the First Value from the List
Cell F2 will contain the formula responsible for returning the last non-empty value from column A, or the first value from the list if column A is empty. Here’s the formula:
=ifna(indirect(ArrayFormula("A"&MATCH(2,1/(A:A<>""),1))), D2)
The ArrayFormula(“A”&MATCH(2,1/(A:A<>””),1)) part of the formula finds the cell ID of the last non-empty cell in column A. The Indirect function then uses that ID to return the value from the corresponding cell. If column A is empty, the formula returns the first value from the list in cell D2.
Formula 2 – The ‘Next Value’ in the List
Cell F3 contains the formula responsible for returning the value next to the one generated by Formula 1 in the list. Here’s the formula:
=iferror(if(counta(A:A)=0, "", index(D2:D11, match(F2, D2:D11, 0)+1)))
The IF function tests whether there are values in column A using the COUNTA function. If there are no values, the formula returns a blank. Otherwise, it uses the INDEX and MATCH combination to find the next value in the list.
Now that we have the two formulas in cells F2 and F3, we can create the data validation drop-down.
List from Range to Enter Values from a List as per the Order in the List
- Select the range where you want the drop-down to appear (e.g., A2:A20).
- Click on “Data” in the menu, then select “Data validation.”
- Configure the data validation settings as shown in the image below:
And that’s it! Users can now only enter or select values in column A from the list, ensuring that they follow the required order.
Don’t you just love these little tricks that make your life easier? If you want to learn more amazing tips and tricks for Google Sheets, head over to Crawlan.com and explore our wealth of knowledge. Happy spreadsheeting!
Sample_Sheet_11920
