Shorten an Excel Data Validation Formula and Return the Correct Column
Question details
Shorten a data validation formula that exceeds Excel's 255-character limit and correct a list formula that returns values from rows instead of columns.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user is creating a data validation rule or dropdown list based on worksheet values.
- Observed behavior
- The original validation formula is 269 characters long, and the list formula returns values in the wrong direction.
Select the affected cell and open Data > Data Validation to check whether the rule uses List or Custom under Allow. Confirm the source range and which cells contain the conditions before editing the formula.
Use INDEX to return a column for a validation list
Use this approach when a dropdown needs values from a column rather than a row.
In INDEX, a row argument of 0 returns the selected column from the specified range. The column number is relative to that range, so 1 selects column AA and 2 selects column AB in this example.
In Excel, open Formulas > Name Manager and select New. Name the formula ColumnList and enter =INDEX('DATABASE'!$AA$2:$AD$5,0,1) in Refers to.
In the named formula, replace the final 1 with the desired column number from 1 through 4, or with a reference to a cell containing that number.
Select the destination cell, open Data > Data Validation, choose List under Allow, and enter =ColumnList in Source. Select OK.

Replace a long custom validation formula with AND and OR
Use a shorter logical formula when the rule checks conditions rather than supplies dropdown values.
Create a Short Custom Validation Rule in WPS Spreadsheets
Use WPS Spreadsheets to apply a concise AND/OR formula that checks worksheet values and returns TRUE or FALSE.
- 1. Open the workbook: Open WPS Spreadsheets and use Open to select your Excel workbook.
- 2. Select the validation cell: Click the cell where you want to apply the rule, then open Data > Data Validation.
- 3. Enter the custom formula: Choose Custom in the validation settings and enter =AND(OR(A32="BIO-ORIENTED",A32="MONO-ORIENTED"),OR(A35="LOW",A35="HIGH")) in the formula field. Adjust the cell references to match your sheet.
- 4. Apply and test: Confirm the settings, then change the referenced cell values to check that valid combinations are accepted.

Frequently Asked Questions
Why does Excel reject my data validation formula?
Excel limits the length of a formula entered in the data validation dialog to 255 characters. Shorten the expression, or use a named formula as the source for a List rule.
How do I make INDEX return a column instead of a row?
Set the row argument to 0 and specify the column number, such as =INDEX('DATABASE'!$AA$2:$AD$5,0,2) for the second column of the range.
Should a custom validation formula return a list?
No. A Custom rule should evaluate to TRUE or FALSE. To display a dropdown, choose List under Data > Data Validation and provide a source range or named reference.
How can I use values from another sheet in a dropdown?
Create a name under Formulas > Name Manager that refers to the source range or an INDEX formula. Then select List in Data > Data Validation and enter the name, such as =ColumnList, in Source.




