looking for some guidance. I’m trying to add validation to a range of cells in a .xlsm file. I want to dynamically add validation to some cells in column C, dependent on whether a value is present in column A. My code seems to correctly create the dynamic name “EndorsementRange” as I can verify it’s in the .xlsm file, but the validation itself is failing. Here’s the relevant portions of the code:

LIBRARIES

import openpyxl  # Library for reading and writing Excel files
from openpyxl.worksheet.datavalidation import DataValidation # Module to add data validation rules
from openpyxl.utils.dataframe import dataframe_to_rows  # Function to convert a DataFrame to rows for openpyxl
from openpyxl.workbook.defined_name import DefinedName  # Import for creating named ranges
import pandas as pd  # Data manipulation and analysis library
import win32com.client as win32  # Library for interacting with Excel and other Windows applications via COM
import xlsxwriter  # Library for creating Excel files with advanced formatting, charts, and images
import xlwings as xw  # Library for automating Excel with Python and enabling Python scripts within Excel
import os  # Library for interacting with the operating system, including file and directory operations
import time  # Library for adding delays

CODE THAT DEFINES THE NAMED RANGE AND ADDS IT TO THE .XLSM FILE

    # Check if the file exists
    if not os.path.exists(file_path):
        print(f"File '{file_path}' does not exist.")
        return
    
    # Load the workbook
    try:
        workbook = openpyxl.load_workbook(file_path, keep_vba=True)
    except Exception as e:
        print(f"Error loading workbook: {e}")
        return

    # Define the Named Range for the endorsements sheet column
    endorsements_sheet = workbook["endorsements"]
    max_row = endorsements_sheet.max_row
    endorsement_range = DefinedName('EndorsementRange', attr_text=f"'endorsements'!$A$2:$A${max_row}")
    workbook.defined_names.add(endorsement_range)

    # Save the workbook with openpyxl
    workbook.save(file_path)  

CODE THAT IS SUPPOSED TO ADD VALIDATION

    # Use xlwings to apply data validation
    app = xw.App(visible=False)
    wb = xw.Book(file_path)
    ws = wb.sheets['CSR24_Checklist']

   # Apply data validation using xlwings to ADD dynamic validation to cells C100-C140 if "Option 2: Existing Endorsement" is selected in column a on the same row.
    for row in range(100, 140):
        cell = f'C{row}'
        try:
            ws.range(cell).api.Validation.Delete()  # Clear any existing validation
            validation_formula = f'IF(A{row}="Option 2: Existing Endorsement", "EndorsementRange", "")'
            print(f"Applying validation to cell {cell} with formula: {validation_formula}")  # Debugging statement
            ws.range(cell).api.Validation.Add(
                Type=3,  # Type 3 is for list validation
                AlertStyle=1,
                Operator=1,
                Formula1=f'=INDIRECT({validation_formula})'
            )
            ws.range(cell).api.Validation.IgnoreBlank = True
            ws.range(cell).api.Validation.InCellDropdown = True
            ws.range(cell).api.Validation.ShowInput = True
            ws.range(cell).api.Validation.ShowError = True
            ws.range(cell).api.Validation.InputTitle = 'Select Endorsement'
            ws.range(cell).api.Validation.InputMessage = 'Select an endorsement from the list if "Option 2: Existing Endorsement" is selected in column A.'
            ws.range(cell).api.Validation.ErrorTitle = 'Invalid Entry'
            ws.range(cell).api.Validation.ErrorMessage = 'Invalid entry'
            print(f"Data validation applied to cell {cell} with formula =INDIRECT({validation_formula})")
        except Exception as e:
            print(f"Error applying data validation to cell {cell}: {e}")

        
    # Save and close the workbook with xlwings
    wb.save()
    wb.close()
    app.quit()

THE VALIDATION FAILS FOR EACH CELL, TERMINAL PRINTS THIS OUT FOR EACH CELL

Applying validation to cell C100 with formula: IF(A100="Option 2: Existing Endorsement", "EndorsementRange", "")
Error applying data validation to cell C100: (-2147352567, 'Exception occurred.', (0, None, None, None, 0, -2146827284), None)

HOWEVER, THAT TYPE OF LOGIC WORKS FINE IF I DON’T USE INDIRECT() AS IN THIS CODE:

    for row in range(start_row, end_row):
        cell = f'A{row}'
        ws.range(cell).value = ''  # Add default value
        ws.range(cell).api.Validation.Delete()  # Clear any existing validation
        ws.range(cell).api.Validation.Add(
            Type=3,  # Type 3 is for list validation
            AlertStyle=1,
            Operator=1,
            Formula1="=validation_lists!$A$1:$A$2"
        )
        ws.range(cell).api.Validation.IgnoreBlank = True
        ws.range(cell).api.Validation.InCellDropdown = True
        ws.range(cell).api.Validation.ShowInput = True
        ws.range(cell).api.Validation.ShowError = True
        ws.range(cell).api.Validation.InputTitle = 'Endorsement Swap Options'
        ws.range(cell).api.Validation.InputMessage = 'Use "Option 1: New Endorsement" if the renewal attachment has not been uploaded to CSR24 yet.nnUse "Option 2: Existing Endorsement" if the attachment was previously uploaded to CSR24'
        ws.range(cell).api.Validation.ErrorTitle = 'Invalid Entry'
        ws.range(cell).api.Validation.ErrorMessage = 'Invalid entry'

I’ve tried hard coding the formula, i’ve tried using a helper function, both with the same results. When I used the help column that has this formula <IF(A{row}=”Option 2: Existing Endorsement”, “EndorsementRange”, “”> I could see the logic working based on the value in column A, and if I manually type the =INDIRECT formula into excel itself it works fine, and recognizes the defined name range.

Any guidance would be greatly appreciated!