I am getting some very strange behavior generating a spreadsheet where the validation (drop down) for a particular column is set in the template we are using to generate the sheet from. First off we are using version 3.6.2. The validation is set using the following formula:
=OFFSET(INDIRECT("LEVEL" & $BA1),0,0,ROWS(INDIRECT("LEVEL" & $BA1)),1)
What it is doing is getting the value from a cell in column BA and concatenating it to the word level. This corresponds to a named cell range on the sheet that it gets the values for the drop down list from. In other words the drop down list will potentially be different for each row of data that is inserted because the formula will possibly resolve to a different named range.
When the sheet generates the drop down is there with a small box in it which is obviously worng. When I open the validation for the column again and do nothing but hit OK, I can then see all the drop down lists populated properly. As you can imagine this is maddening.
Is it possible that the validation is being applied before the named ranges are set? or the data that the named ranges are referencing is not populated yet? I don't think so since all that stuff is on the template. However the validation is also on the template and it is acting very strange.
I have attached the template. The validation is set on column AJ. the data that will determine which named list is used is in column BA. starting at row 19, if you add a number between 1 and 17 to BA you will see the drop downs in AJ change dynamically to correspond to the named range.
This is the behavior that I cannot duplicate when the sheet is generated by aspose without opening the validation for the row and clicking OK after the sheet is generated (When excel is doing the work).
I am eagerly anticipating your findings.
Thanks In Advance,