What I am trying to do:

Create a conditional dropdown list such that the selection from one dropdown drives the choices in a 2nd dropdown. For example, dropdown in cell A1 has choices "Books", "Animals". If "Books" is chosen, a dropdown in B1 is populated with a list of books to choose from. If "Animals" is chosen, a the dropdown in B1 is instead populated with a list of animals to choose from.

What I have attempted:

I am able to do this in Excel directly by creating a 2nd sheet with all possible values for the 2nd dropdown (B1), and then creating named references with names that match the choices in the first dropdown (A1) and point that reference to the range that contains the values for that particular A1 option. I then set the Values property in the Excel UI for Data List B1 to "=INDIRECT(A1)".

When I try to create this rule programmatically using Infragistics, I use the following code:

ListDataValidationRule listDataRule = new ListDataValidationRule();
listDataRule.ErrorStyle = DataValidationErrorStyle.Warning;
listDataRule.ShowDropdown = true;
listDataRule.SetValuesFormula("=INDIRECT(A1)", "B1");

The error:

When I reach the last line of code, I get the following error message:

"The ValuesFormula must be set to a string constant or a reference to a single cell or region in the same Workbook."

I feel like I am so close, but it seems like this SetValuesFormula() method doesn't allow anything but a cell or range to be entered - no functions. I have also tried using SetValues() but this value is treated literally rather than an equation until I open the Data Validation Excel UI window and press OK.

Any advice on whether this is supported, will be supported in the future, or if there is another way to accomplish what I am trying to do is appreciated.