How to set non-adjacent cell range for XlsxWriter Data Validation

user_3416725 0
Viewed 189

I am using the Python XlsxWriter module to add a drop down list using the method data_validation currently I have the set up so I drop duplicates on a Pandas Series and convert that into a list and set the values for the drop down list like so:

# set the column values to be a list
worksheet.data_validation(xlsx_header_letter + '2:' + xlsx_header_letter + str(1+n_rows),
{'validate': 'list', 'source': col_dropped_duplicates})

This works fine however if the list exceeds 255 characters as according to Excel it will not allow it to validate. Instead they mention you should rather use a range like so:

# Using 'source'.
worksheet.data_validation('B10', {'validate': 'list',
                              'source': '=$E$4:$G$4'})

The above example is from their docs. However I cannot create a dynamic range like this as the Series I have has reduced values (I dropped duplicates on it). So I want to be able to only select the cells for those values.

Is this possible?

I tried to use something similar to this:

worksheet.data_validation(xlsx_header_letter + '2:' + xlsx_header_letter + str(1+n_rows),
{'validate': 'list', 'source': '=($C$2:$C$2, $C$7:$C$7)'})

But when I saved the excel it was corrupt.

UPDATE: I also tried to create another sheet with the dropped duplicate values and set the range to that sheet's name and range. But it still saves a corrupt file. Something similar to:

worksheet.data_validation(
xlsx_header_letter + '2:' + xlsx_header_letter + str(1+n_rows), {'validate': 
'list', 'source': '=' + new_sheet_name + '!A2:A4'})
xlsxwriter pandas python
1 Answers

The above example is from their docs. However I cannot create a dynamic range like this as the Series I have has reduced values (I dropped duplicates on it). So I want to be able to only select the cells for those values.

Is this possible?

As far as I know that isn't possible, in Excel (even apart from XlsxWriter).

If you try to select a non-contiguous range in an Excel data validation you will get a message like this: "You may not use reference operators (such as unions, intersections, and ranges) or any constants for Data Validation criteria".

You can verify that yourself. If it is possible to it in Excel then it will probably be possible in XlsxWriter.

user_102380
Related