`write_formula` in `xlsxwriter` in Python

Question

I am using xlsxwriter in Python to create an Excel xlsx.

Consider the code

import xlsxwriter

wb = xlsxwriter.Workbook('Book.xlsx')
ws = wb.add_worksheet('Sheet1')

data = (['Average Start Date','365'],['AAA','2'])

worksheet.add_table(1, 1, 4,4, {'data':data,'first_column':True,'style':'Table Style Light 1','autofilter': False,'columns':[{'header':'Attribute'},{'header':'Value'}]})


ws.write_formula('A4', '=IF(A3>1,"Yes", "No")')
ws.write_formula('A5', '=VLOOKUP("Average Start Date",Table1,2,FALSE)/365')

After opening the xlsx file with Excel the cell A4 displays No, the formula gets computed. On the other hand the cell A5 shows #NAME?.

If I select the cell A5, press F2, which is for editing the formula, and press Enter without apparently modifying anything the formula in A5 gets computed.

Why is this? Is there an error in the way I am adding the formula to A5? Is there a way to make that formula get computed automatically?

Answer 1

This is not an XlsxWriter error, it is an Excel error. You would get the same result if you entered the formula manually and hit return.

The #NAME? error is caused by the fact that the formula refers to a data range called Table4 which doesn't exist.

Also, you have a syntax error in your code snippet. It should be:

ws = wb.add_worksheet('Sheet1')

# Or just
ws = wb.add_worksheet()

Also, you should add wb.close() to the end of the program.

[Edit] Answer in the comment by this answer's author

It looks like Table1 needs to be written as Table1[] in the formula, like this: =VLOOKUP("Average Start Date",Table1[],2,FALSE)/365'). It will be displayed as Table1