How to create a drop-down list in a cell
Make an in-cell drop-down list in Grid by Zaweba with a data validation list rule, typed in or taken from a range, so people pick from set choices.
Set a data validation rule of the list kind on the cell. In Grid by Zaweba, data validation can restrict a cell to a list of allowed values, and the cell then shows an in-cell drop-down so people choose an entry instead of typing it. The list can be typed straight into the rule or taken from a range on any sheet.
Why a drop-down helps
Typed entries drift. One person writes Paid, another paid, a third PAID with a space after it, and suddenly a filter or a COUNTIF misses half the rows. A drop-down keeps every entry spelled the same way, which makes sorting, filtering, pivot tables and formulas all behave. It's also quicker for the person filling in the sheet.
Making the list step by step
Select the cells that should have the drop-down, which may be a whole column of a table. Open data validation and choose a list as the kind of rule. For a short, fixed set of choices, type them in separated by commas, such as Yes, No, Maybe. For anything longer or anything that might change, point the rule at a range of cells holding the choices.
Apply the rule and click one of the cells. A small arrow appears beside it, and clicking it shows the choices.
Keeping the choices in a range
A range of choices is easier to maintain than a typed list. Put the options on a separate sheet, perhaps called Lists, one per cell down a column. Give that range a name in Name Manager, such as Statuses, and use the name in the validation rule. When you need a new option, add it to the list and every drop-down that uses the name can offer it.
Even better, make the list of choices a table, or build it with a formula. A UNIQUE formula over a column of existing entries, wrapped in SORT, produces a tidy, ordered list that grows by itself, and a spill reference such as A2# points the validation rule at the whole result.
Adding a message and an alert
Data validation can show an input message when someone selects the cell, a short note such as Choose the order status. That helps when the sheet is shared with people who haven't seen it before.
It also decides what happens if someone types a value that isn't on the list. A Stop alert refuses it, a Warning alert asks if they're sure, and an Information alert lets it through with a note. For a status column that feeds reports, Stop is usually right.
Checking existing data
Adding a drop-down to cells that already hold values doesn't change them. Circle Invalid Data marks any existing entries that break the rule, so you can fix old typos after setting up the list.
Keeping lists clean
Copying cells carries their validation with them in Grid, since its own clipboard keeps validation along with formulas and formats. Pasting a plain value over a validated cell from another program may not, so check the column after a big paste.
Keep the choice list free of blanks and stray spaces. A trailing space makes a choice that looks the same as another but doesn't match it in formulas.
Drop-downs and other validation rules are saved in .xlsx and .zagr files, so a sheet you hand to someone else keeps its lists. They're not written to .ods, so if the file is going to a LibreOffice user, save it as .xlsx instead.
A drop-down takes a minute to set up in Grid by Zaweba and saves hours of tidying later, which is why they turn up in nearly every shared sheet worth keeping.
