[Solved] Error while use (double) Indirect function

Dec 22, 2015 at 9:17 AM
Edited Dec 22, 2015 at 9:18 AM
In a worksheet I have a table. In one of the columns there will be a code of 3 characters. Target is another column with data validation, based on the column with that code. Because I don't know what code will be entered, the formula in the validated column must be the same for the whole column.

In Excel it works fine. For each possible code I create a named range. After that, I use the following code in the List Datavalidation source for the whole column:
The code I try to use:
Dim myValidat = mySheet.DataValidations.AddListValidation("A2:A7))
With myValidat
   .ErrorStyle = OfficeOpenXml.DataValidation.ExcelDataValidationWarningStyle.warning
   .ShowErrorMessage = True
   .ShowInputMessage = True
   .PromptTitle = "prompttitle"
   .Prompt = "prompt"
   .Formula.ExcelFormula = "INDIRECT(INDIRECT(ADDRESS(ROW();1;3)))"
End With
The first indirect is to get the value in the addressed cell.
The second indirect is to call the named range with the value in that cell

In Excel it works fine, but after using it in VB.NET and opening in Excel, that worksheet in that document is in errorstate, starts repairing, and delete the validation of that column.

What I want is to accomplish this with code in VB.NET and using epplus. Please tell me what I'm doing wrong!

Edit; I posted accidentally a topic in the Issue forum. There you will find an attachment: https://epplus.codeplex.com/workitem/15398
Dec 25, 2015 at 1:23 PM
You should use comma instead of semicolon in your formula. Hope this helps.

Marked as answer by alexvanb on 12/28/2015 at 1:16 AM
Dec 28, 2015 at 8:16 AM
Thank you very much swmal, that was the issue! I already translated the formulas from Dutch to English, but did not think about the formula delimiter.
It's working great now!