Hi,I have a Excel workbook/spreadsheet with 1 visible sheet and 6 hidden sheets.Formula cells on visible sheet (Sheet 0) are pointing to cells on hidden sheets within the same workbook.One of the formulas is to calculate age based on two dates. When I try to retrieve calculated value of cell containing age, I am getting a string, "'Unformatted Values'!AD13" where 'Unformatted Values' is one of the hidden sheets.I have seen methods to link external workbooks and to evaluate all formulas usingFormulaEvaluator mainWorkbookEvaluator = wb.getCreationHelper().createFormulaEvaluator();Map<String, FormulaEvaluator> workbooks = new HashMap<String, FormulaEvaluator>();workbooks.put(wb.getSheetName(0), mainWorkbookEvaluator);workbooks.put(wb.getSheetName(1), mainWorkbookEvaluator);workbooks.put(wb.getSheetName(2), mainWorkbookEvaluator); etc.,When trying to setup referenced workbooks using,mainWorkbookEvaluator.setupReferencedWorkbooks(workbooks);I am getting the following exception about attempting to register same workbook under different names.java.lang.IllegalArgumentException: Attempted to register same workbook under names 'Unformatted Values' and 'NP T-Scores' at org.apache.poi.ss.formula.CollaboratingWorkbooksEnvironment.(CollaboratingWorkbooksEnvironment.java:114) at org.apache.poi.ss.formula.CollaboratingWorkbooksEnvironment.setup(CollaboratingWorkbooksEnvironment.java:75) at org.apache.poi.ss.formula.CollaboratingWorkbooksEnvironment.setupFormulaEvaluator(CollaboratingWorkbooksEnvironment.java:89) at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.setupReferencedWorkbooks(HSSFFormulaEvaluator.java:103) at com.emmes.hbb.POICalculator.main(POICalculator.java:61)How can I create a link/reference between visible sheet and all the other hidden sheets for evaluating formulas?Thanks for your help.
-- Sent from: http://apache-poi.1045710.n5.nabble.com/POI-User-f2280730.html
