0

I know that error "We Found A Problem With Some Content In Excel" has been asked a lot of times, but I haven't found my root cause for this problem.

In my excel file this error appears after I switch formula

=IFERROR(XLOOKUP([@Column1];Sheet1[Column2];Sheet1[Column3]);0)

to following formula

=IFERROR(XLOOKUP([@Column1];Value(Sheet1[Column2]);Sheet1[Column3]);0)

Basically, I am just adding Value() function as values in Column2 often appear to be "Number stored as text".

Data is added to the template file (it contains mentioned formulas) using GemBox. The algorithm is I have template, run the app that adds data to the file, then I open file and everything is good.

But as I correct the formula adding Value() function it breaks down as I try to open the file in the last step.

The only thing that comes to my mind is that xlookup function accepts the array of data, so I can't use Value function by passing array into it. But still, if I change my formula manually, it works well

Roman
  • 23
  • 5
  • Can you try replacing ';' with ','? If that doesn't work, can you upload somewhere your Excel file which has an older version of your formula so that I can try reproducing your issue? – Mario Z Sep 11 '20 at 08:17
  • @MarioZ yes, I've tried doing, but ';' is the right way for working with my excel version. The problem is actually not in formula, as it is ok when added manually. Error occurs when I add the formula using GemBox – Roman Sep 15 '20 at 08:57
  • note that internally, formulas are always written in ',', not with ';'. It is just that the language pack that you have installed is showing you formulas like this. In other words, that is the GUI thing, but inside the file it is a comma character. If you think about it, this is how it must be because otherwise no one without your language pack installed would be able to open that file. – Mario Z Sep 15 '20 at 09:41
  • so again, try replacing the character, and if the problem remains try uploading somewhere your file so that I can investigate it. – Mario Z Sep 15 '20 at 09:42

0 Answers0