adding formula to data validation list dropdown

[Issa Saleh]

Is it possible to add a formula to data validation list dropdown.

Example:

I have two drop downs,

I want to add in the "second data validation drop down list" this formula:

=INDIRECT(A1)

If i use the type.formula i will loose the list functionality.

[Alex Harson James Henso]

Hi Issa,

 

XlsIO supports using formulas like =INDIRECT(A1) in data validation dropdown lists.

 

For more information, please refer to the following Knowledge Base article:

How to create a dependent dropdown list using XlsIO

 

Please feel free to reach out if you have any further questions or need assistance.

 

Regards,

Alex Harson James H

[Elise Helen]

Absolutely! The standard way to create a dependent (cascading) drop-down is by setting the Data Validation type to List and using =INDIRECT(A1) Baseball Bros in the Source field. If you use Formula instead of List in the validation settings, it removes the drop-down menu feature

[Karthika Santhana Krishnan]

Hi Issa,

 

As you mentioned, the correct way to build a dependent dropdown is by setting the Data Validation type to List and using a formula such as =INDIRECT(A1) in the Source field.

 

XlsIO mirrors this behavior. When you configure AllowType = ExcelDataType.User together with FirstFormula = "INDIRECT($B8)", it works the same as Excel’s List type with formula source, ensuring the dropdown remains available and cascading lists function properly.

 

Regards,

Karthika

[Allen Brian]

Yes, you can do this, but not by switching the validation type to Formula, because that changes the validation rule instead of the list source.

For a dependent dropdown, keep the validation type as List and set the list source/range to the formula. In Excel this is typically:

=INDIRECT(A1)

where A1 contains the name of a named range.

If you're using Syncfusion XlsIO, look for the property that sets the list source (or first formula for list validation), rather than changing the validation type to Formula. That way the dropdown behavior is preserved while the list is populated dynamically.

I've used this approach for cascading dropdowns before, and it works well as long as the referenced named ranges exist and match the values in the first dropdown wheelie life exactly.

[Alex Harson James Henso]

Hi Issa,

As we mentioned earlier, when you configure AllowType = ExcelDataType.User together with FirstFormula = "INDIRECT($B8)", it works the same as Excel’s List type with formula source, ensuring the dropdown remains available and cascading lists function properly.

 

Regards,

Alex Harson James H

[Huan Maire]

Turning Data Validation type to List and inserting =INDIRECT(A1) into the Source field is the conventional method for making a dependent (cascading) drop-down. In the validation settings, the drop-down menu feature is removed when you use Formula instead of List.

[Alex Harson James Henso]

Hi Huan,

 

Thank you for contacting us.

Based on Microsoft Excel's behavior, when =INDIRECT(A1) is entered as a Custom Type (Formula Type) in Data Validation, the drop-down list is not displayed. However, when =INDIRECT(A1) is entered as a List Type, the drop-down list is displayed correctly. This is the expected behavior in Microsoft Excel.

 

Regards,
Alex Harson James H