Creating Datavalidation That Evaluates To An Error With Openpyxl

Using python and openpyxl I'm trying to create dependent list of data with DataValidation like in example from this article. DataValidation works fine when I'm pointing directly t

Solution 1:

So my friend excel-specialist pointed me to , vs ;

My bad was that I tried to put into openpyxl formula separated by semicolon as in my excel but library uses only commas for this purpose regardless of windows regionale settings.

Again fooled by windows... Hope this will save others the many hours I wasted on finding out the error.

#working formula, replaced semicolons with commas
formula2 = "OFFSET(DATA!A2,0,MATCH(A1,DATA!1:1,0)-1,COUNTA(OFFSET(DATA!A:A,0,MATCH(A1,DATA!1:1,0)-1))-1)"