I'm using the POI-based Jxls library to generate spreadsheets which contain macros which refer to other worksheets. I'm having a problem when the formula refers to an empty variable, and showing "#VALUE" instead of just a blank.
For example, the "original" worksheet (call it worksheetA) is correctly populated using Jxls variables. E.g., I put this in worksheetA, cell A2
${employee.salary}
The above refers to a collection, so if there are 10 employees, 10 rows will appear in the generated worksheetA. Note that the size of the collection is unknown in terms of the template, but I can retrieve it using the java class which generates the spreadsheet if necessary.
In another worksheet (call it worksheetB), I'm populating cells based on the contents of worksheetA.
For example, in worksheetB, I'll populate say 20 rows of column A with this formula:
$[worksheetA!A2]
The first 10 rows of worksheeB get calculated properly. But, the remaining 10 rows show "#VALUE", since there is no corresponding employee (i.e. there are only 10 employees).
It's kind of a contorted explanation, but basically a formula referring to a cell beyond those which have been populated by the Jxls collection returns "#VALUE" and I'd like to get rid of it.
The problem is that the ${employee.salary} expands into a multiple rows on the spreadsheet, depending on the number of items in the collection. Excel considers this to be an insertion, so any formulas previously pointing to the first 10 rows will now be pointing to the 11th and onward. I wanted them to stay pointing to the 1 through 10 rows (or 1-n rows), so the workaround for me was to insert the data into worksheets that were not referenced by formulas, and then automatically run a macro which copied the those worksheets to the ones which were referenced by the macro, thus avoiding the insertion.