I have a Python script that produces an Excel file with columns that can be used to input chapters, sections, and subsections from a book that SMEs can use to write citations. I need to be able to process these citations programmatically, so I use data validation to ensure that they enter the exact text that the program is expecting.
It works almost perfectly, but it has a flaw: the user can enter arbitrary input in the section column if the chapter column is unfilled, and in the subsection column if the section column is not filled. How can I modify this script so that if the chapter column is blank, the section column must be blank, and same for the subsection column if the section column is blank?
MWE follows:
import xlsxwriter
from xlsxwriter.utility import xl_range_formula, xl_rowcol_to_cell
N = 10
chapters = {'chapter 1 (1)': ['section 1 (1.1)', 'section 2 (1.2)'],
'chapter 2 (2)': ['section 1 (2.1), section 2 (2.2)']}
all_sections = {'section 1 (1.1)': ['subsection 1 (1.1.1)', 'subsection 2 (1.1.2)'],
'section 2 (1.2)': ['subsection 1 (1.2.1)', 'subsection 2 (1.2.2)'],
'section 1 (2.1)': ['subsection 1 (2.1.1)', 'subsection 2 (2.1.2)'],
'section 2 (2.2)': ['subsection 1 (2.2.1)', 'subsection 2 (2.2.2)']}
key = 'Book'
workbook = xlsxwriter.Workbook('dependent_dv_mwe.xlsx')
main_worksheet = workbook.add_worksheet('Evaluation')
chapter_worksheet = workbook.add_worksheet('Chapters')
section_worksheet = workbook.add_worksheet('Sections')
keys_worksheet = workbook.add_worksheet('Keys')
chapter_start_col = 0
section_start_col = 0
chapter_max_row = 0
section_max_row = 0
keys_worksheet.write(0, 0, key)
for col, (chapter, sections) in enumerate(chapters.items()):
chapter_worksheet.write(0, chapter_start_col + col, chapter)
for j, section in enumerate(sections):
chapter_worksheet.write(j + 1, chapter_start_col + col, section)
chapter_max_row = max(chapter_max_row, j + 1)
workbook.define_name(key, xl_range_formula('Chapters',
0, chapter_start_col,
0, chapter_start_col + col))
chapter_start_col = chapter_start_col + col + 1
for col, (section, subsections) in enumerate(all_sections.items()):
section_worksheet.write(0, section_start_col + col, section)
for j, subsection in enumerate(subsections):
section_worksheet.write(j + 1, section_start_col + col, subsection)
section_max_row = max(section_max_row, j + 1)
section_start_col = section_start_col + col + 1
# main_worksheet.data_validation(
# 'A1', {'validate': 'list',
# 'source': '='
# + xl_range_formula('Keys', 0, 0, len(all_chapters), 0),
# 'ignore_blank': True}
# )
main_worksheet.write(xl_rowcol_to_cell(0, 0),
'Chapter')
main_worksheet.write(xl_rowcol_to_cell(0, 1),
'Section')
main_worksheet.write(xl_rowcol_to_cell(0, 2),
'Subsection')
for j in range(1, N + 1):
cell1 = xl_rowcol_to_cell(j, 0)
cell2 = xl_rowcol_to_cell(j, 1)
cell3 = xl_rowcol_to_cell(j, 2)
main_worksheet.data_validation(cell1, {'validate': 'list',
'source': '=%s' % key,
'ignore_blank': True})
main_worksheet.data_validation(
cell2,
{'validate': 'list',
'source': '=INDEX(%s,,MATCH(%s, %s, 0))'
% (xl_range_formula('Chapters', 1, 0,
chapter_max_row,
chapter_start_col),
cell1,
xl_range_formula('Chapters', 0, 0, 0,
chapter_start_col)),
'ignore_blank': True}
)
main_worksheet.data_validation(
cell3, {'validate': 'list',
'source': '=INDEX(%s,,MATCH(%s, %s, 0))'
% (xl_range_formula('Sections', 1, 0,
section_max_row,
section_start_col),
cell2,
xl_range_formula('Sections', 0, 0, 0,
section_start_col)),
'ignore_blank': True}
)
workbook.close()
For what you require you need to set the data_validation ignore blank setting to False, 'ignore_blank': False for the Section and Subsection Data Validation settings.
That way if someone types into a cell in those columns they will get an error and can only select from the drop down created after the previous column is populated.