Free Support Forum - aspose.com

Validation lookup formula error

Laurence,

I’m having a problem with the Aspose excel component that I’d like you to look into. I am setting cell.formula programatically to a VLOOKUP for each row. If the formula looks like this:

=VLOOKUP(H{r},ValidCategories!$A$2EmbarrassedB$62000,2,FALSE)

It works with no problems. If I enhance the formula to filter out N/A values like this, I get an error when attempting to save the spreadsheet.

=IF(ISNA(VLOOKUP(H{r},ValidCategories!$A$2EmbarrassedB$62000,2,FALSE)), “”, VLOOKUP(H{r},ValidCategories!$A$2EmbarrassedB$62000,2,FALSE))

Note: I’m replacing the {r} with the current row number in my code this isn’t coming from a template.

The error produced is:

Invalid formula in data validation settings.

Stack trace:

at Aspose.Excel.Record.aj.c(String A_0)
at Aspose.Excel.Record.aj.a(Validation A_0, String A_1)
at Aspose.Excel.Record.aj.a(Validation A_0)
at Aspose.Excel.Worksheet.c(bn A_0)
at Aspose.Excel.Worksheets.b(bn A_0)
at Aspose.Excel.Worksheets.a(UCOMIStream A_0)
at Aspose.Excel.Record.c5.a(c3 A_0, t A_1, Worksheets A_2)
at Aspose.Excel.Worksheets.a(String A_0, SaveType A_1, FileFormatType A_2, HttpResponse A_3)

Thanks!
Kris

Hi Kris,

I think this problem is caused by the data validation settings. Do you set any custom data validation criteria? Please send my your designer spreadsheet and source code. I will check this problem right now.

Hi Kris,

I think this problem is caused by the data validation settings. Do you set any custom data validation criteria? Please send me your designer spreadsheet and source code. I will check this problem right now.

Laurence,

In my code I do something along these lines to set the formula. The first example works 100%, switching out the formula to the more complex version causes the error (no other code changes).

string formula = “=VLOOKUP(H{r},ValidCategories!$A$2EmbarrassedB$62000,2,FALSE)“

string formula = “=IF(ISNA(VLOOKUP(H{r},ValidCategories!$A$2EmbarrassedB$62000,2,FALSE)), “”, VLOOKUP(H{r},ValidCategories!$A$2EmbarrassedB$62000,2,FALSE))”


code inside loop:

w.Cells[rowCount, i].Formula = formula.Replace(”{r}”, (rowCount+1).ToString());


Since the only change is the formula, not sure what it can have to do with the validation code, but here is what it looks like:

private void DefineCategoryDataValidation(Worksheet w, int rowCount, byte columnNumber)
{
const string FORMULA = “=CategoryNames” ;
CellArea area = new CellArea();
area.StartRow = 1 ;
area.EndRow = rowCount ;
area.StartColumn = columnNumber ;
area.EndColumn = columnNumber ;

// if we didn’t find the existing validation, then create a new one
Validation CategoryValidation = FindValidation(w, FORMULA) ;
if (CategoryValidation == null)
{
CategoryValidation = AddManufacturerDataValidation( w ) ;
CategoryValidation.Formula1 = FORMULA ;
CategoryValidation.ErrorMessage = “Category name does not match the list of valid values.” ;
}

private Aspose.Excel.Validation AddManufacturerDataValidation(Worksheet w)
{
Validations validations = w.Validations;
Validation validation = validations[validations.Add()];
validation.Type = Aspose.Excel.ValidationType.List;
validation.Operator = OperatorType.None;
validation.ShowInput = false ;
validation.IgnoreBlank = true ;
validation.InCellDropDown = true ;
validation.AlertStyle = ValidationAlertType.Warning ;
validation.ErrorMessage = “Manufacturer name does not match the list of valid values.” ;
validation.Formula1 = “=MfrNames”;
return validation ;
}

if (CategoryValidation != null)
CategoryValidation.AreaList.Add(area) ;
}

Kris,

I think this problem is caused by ISNA excel function. In previous version, it's not supported. I added this feature in v2.5. Please download it at http://www.aspose.com/Products/Aspose.Excel/Fixes/Aspose.Excel.zip and have a try.

If you still find this problem, please send me your designer file and more code. Thanks.