Reference Different Worksheets with Pandas ExcelWriter

Question

I am writing cascading dropdowns to Excel with Pandas ExcelWriter and I would like to put my validation references on a different sheet than my dropdowns. I have the functionality I need on one sheet but when I create

worksheet2 = writer.sheets['Sheet2']

I can't reference it to check the data validation lists.. I can write to Sheet2 I just can't reference it my for loop. This is what I have for one sheet:

import numpy as np
import pandas as pd


newVals = np.empty([100,4])


df = pd.DataFrame(newVals)
n_rows = df.shape[0]



#Write to xlsx file 
writer = pd.ExcelWriter('/trial/DF_New.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1')


#Assign workbook and worksheet
workbook = writer.book
worksheet = writer.sheets['Sheet1']


#Creation of unlocked format
unlocked = workbook.add_format({'locked': False})
worksheet.set_column('B:F', 25, unlocked)


#Set Values 
worksheet.write('I1', 'Cobra')
worksheet.write('I2', 'Fault')
worksheet.write('I3', 'IT')
worksheet.write('I4', 'Quality')
worksheet.write('I5', 'Misc')
worksheet.write('I6', 'Alignment')

worksheet.write('J1', 'Mamba')
worksheet.write('J2', 'Fault')
worksheet.write('J3', 'IT')
worksheet.write('J4', 'Quality')
worksheet.write('J5', 'Misc')
worksheet.write('J6', 'Mamba_Lag')

worksheet.write('K1', 'Python')
worksheet.write('K2', 'Fault')
worksheet.write('K3', 'IT')
worksheet.write('K4', 'Quality')
worksheet.write('K5', 'Misc')
worksheet.write('K6', 'Viscosity')

#Set Granular Issue Values 
worksheet.write('L1','Fault')
worksheet.write('L2','Electrical_Setup')
worksheet.write('L3','Fluidics_Issue')

worksheet.write('M1','IT')
worksheet.write('M2','No_Comms')
worksheet.write('M3','PC_Issues')

worksheet.write('N1','Quality')
worksheet.write('N2','Internal_Quality')
worksheet.write('N3','External_Quality')

worksheet.write('O1','Misc')
worksheet.write('O2','Personnel')
worksheet.write('O3','Other')

worksheet.write('P1','Alignment')
worksheet.write('P2','Left Over')
worksheet.write('P3','Right Over')

worksheet.write('Q1','Viscosity')
worksheet.write('Q2','50-75%')
worksheet.write('Q3','>75%')

worksheet.write('R1','Mamba_Lag')
worksheet.write('R2','At_Setup')
worksheet.write('R3','Other_Time')

#Create dropdown menus and check values against dropdowns
worksheet.data_validation('B2:B'+str(1+n_rows), {'validate' : 'list', 'source': ['Cobra', 
'Mamba', 'Python']})

for i in range(n_rows):
 worksheet.data_validation('C'+str(2+i), {'validate' : 'list', 'source': '=INDEX($I$2:$L$6, 0, 
MATCH($B$'+str(2+i)+', $I$1:$L$1, 0))'})
worksheet.protect()


worksheet.data_validation('C2:C'+str(1+n_rows), {'validate' : 'list', 'source': ['Fault', 
'IT', 'Quality', 'Misc', 'Alignment', 'Viscosity', 'Mamba_Lag']})

for i in range(n_rows):
 worksheet.data_validation('D'+str(2+i), {'validate' : 'list', 'source': '=INDEX($L$2:$R$3, 0, 
MATCH($C$'+str(2+i)+', $L$1:$R$1, 0))'})
worksheet.protect()


#Close the workbook
workbook.close()

I need to reference the validation lists in worksheet.data_validation with but I have corrupted the file when I tried, I assume I need to reference it at 'source', something like:

 {'validate' : 'list', 'source': '=Sheet2(INDEX($L$2:$R$3, 0, 
 MATCH($C$'+str(2+i)+', $L$1:$R$1, 0)))'})

Answer 1

I need to reference the validation lists in worksheet.data_validation with but I have corrupted the file when I tried, I assume I need to reference it at 'source', something like

I don't think Excel supports that type of dynamic list or formula in conditional formats.

If I manually insert the formula from your program, =INDEX($I$2:$L$6, 0, MATCH($B$2, $I$1:$L$1, 0)), then Excel gives this error:

The Formula currently evaluates to an error. Do you want to continue?