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.
This has already been reported (issue id 97840) and fixed. I believe the fix has already been released in an SR. Try upgrading to the latest SR for 11.2 and let me know if you still have the issue.
Thanks Mike,
I am using the evaluation version. I am considering purchasing for this feature alone, so I wanted to make sure it is working properly before buying. There doesn't seem to be a way to patch the evaluation version and the files contained within are from November.
Is it possible to get an updated evaluation version or some guarantee that this has been resolved before I make the purchase?
Mike Dour said:There is an overload of the SetValuesFormula which takes a CellReferenceMode value. You can specify R1C1 if you want to provide and R1C1 formula string.
Perfect. I had missed that one. It did the trick. I've had the boss man place the order.. Thanks!
There is an overload of the SetValuesFormula which takes a CellReferenceMode value. You can specify R1C1 if you want to provide and R1C1 formula string.
I don't think there is any way to get the column letter directly, but there is an overload of WorksheetCell.ToString which will take a CellReferenceMode value. If you specify A1, you will get the cell address in the A1 mode and then you can get the letters from the beginning of the returned string.
Thanks! That has done the trick for my example.
However, of course, I need to now make this dynamic. In trying to use CellReferenceMode of R1C1, I receive the "Invalid Formula" exception again. This isn't a deal breaker for me, though.
Switching back to CellReferenceMode of A1, I'm curious if there is a built in way to retrieve the Excel column letter (A, B, C, ... ZZ, AAA, etc) from a WorksheetCell, WorksheetColumn, or ColumnIndex? Or must I just write code to map the ColumnIndex to the letter myself?
Hello,
There is a check box in the platform installer to install the latest service release. You can run the platform installer again and modify your installation and check the option for installing updates. This will find, download, and install the latest available service release.
There are more details on the platform installer in the following blog post:
http://blogs.infragistics.com/blogs/ambrose_little/archive/2011/06/09/the-new-infragistics-install-experience.aspx
Let me know if you have any questions with this matter.