Data Validation and Dependent Dropdowns
Control inputs and chain dropdown lists.
The Open buttons send the prompt text as currently shown below to that site only when you click. Fill in the placeholders first.
Set up data validation and dependent dropdown lists. App: [SPREADSHEET_APP]. Fields to control and their allowed values: [FIELDS_AND_VALUES]. Relationships (for example country determines city): [DEPENDENCIES]. Sample lists (fake): [SAMPLE_LISTS]. Provide step-by-step instructions and formulas: 1. A basic dropdown from a list on a separate sheet; use a named range or table so new values appear automatically. 2. Number, date, and text length validation with custom error messages that tell users what to enter. 3. Custom formula validation (for example =COUNTIF($A:$A, A2)=1 to prevent duplicates). 4. Dependent dropdowns: a layout for the lists and the formula to return the second list based on the first (INDIRECT approach for fixed lists, and a more robust alternative with OFFSET/MATCH or FILTER). 5. How to clear the dependent cell when the parent changes (script option or a manual check) and the limits of formulas. 6. Protecting the list sheet and input cells. 7. Handling pasted values that bypass validation, and how to find invalid entries (formula to flag). 8. Documentation for users in the sheet. Warn about spaces and special characters in named ranges used with INDIRECT, and provide a test script of six cases. Include accessible error messages.
Fill in before use
Optional: type your details and the prompt above updates. Nothing is saved or sent.
Replace each bracketed placeholder with your own details, then paste into Any AI or another assistant.