I'm using a combination of xlrd, xlutils.copy and xlwt to copy an existing .xls workbook (including formulae) and have found that, while xlwt's formula parsing handles functions with INDEX() in them, the resultant workbook is populated by #VALUE wherever they appear. These can be removed by manually F2 then hit return in Excel but I wanted a more automated solution.
One fix I found by fluke appears to be to go into xlwt\ExcelMagic.py and change:
'INDEX' : ( 29, 2, 4, 'R', 'RVVV'),
to
'INDEX' : ( 29, 2, 4, 'V', 'RVVV'),
'R' and 'V', which I'm guessing stand for something like Reference and Value, are described as "func return type". What does that mean and will the change I've made break anything?
Thanks