Here is the code. So the way I currently have it, it does increment from cell F2, to F3, to F4 but the formula doesn't change it just continues with 'C2-E2' and I need it to change as it increments so next would be C3-E3.
import openpyxl as xl
import os
import pandas as pd
import xlsxwriter
import xlrd
input_dir = 'C:\\work\\comparison\\NNM'
Summary = 'C:\\work\\comparison\\Summary.xlsx'
newFile = 'C:\\work\\comparison\\Comparison.xlsx'
files = [file for file in os.listdir(input_dir)
if os.path.isfile(file) and file.endswith(".xlsx")]
i=0
ii=0
wb3=xlsxwriter.Workbook(newFile)
ws3=wb3.add_worksheet('Comparison')
format = wb3.add_format()
format.set_align('center')
format.set_align('vcenter')
format.set_bold(True)
format.set_font_size(14)
format.set_bg_color('#C9C9C9')
format.set_border(style=1)
formula=('=C2-E2')
ws3.write_row("A1:Q1", ['Name','Segment','Chainage(ft)','Segment','Chainage(ft)','Difference(ft)','Segment',' Chainage(ft)','Segment','Chainage(ft)','Chainage Difference(ft)','Stops','Stops','Distance','Distance ','Distance Difference (ft)','Comments'], format)
ws3.set_column(1, 17, 35)
wb3.close()
wb3 = xl.load_workbook(newFile)
ws3 = wb3.worksheets[0]
wb2 = xl.load_workbook(Summary)
ws2 = wb2.worksheets[1]
for file in files:
input_file = os.path.join(input_dir, file)
wb1=xl.load_workbook(input_file)
ws1=wb1.worksheets[0]
series = pd.read_excel(file,usecols = "F",squeeze = True)
counts = series.value_counts()
z = counts[0]/2
ws3[f'A{i+2}']=ws1['A1'].value[28:]
ws3[f'D{i+2}']=ws1['B4'].value
ws3[f'E{i+2}']=ws1['D4'].value
ws3[f'I{i+2}']=ws1['B'][-1].value
ws3[f'J{i+2}']=ws1['D'][-1].value
ws3[f'O{i+2}']=ws1['E'][-1].value
ws3[f'N{i+2}']=ws2[f'I{ii+6}'].value
ws3[f'M{i+2}']=z
ws3[f'F{i+2}']=formula
i += 1
ii +=1
wb3.save(newFile)