0

I have a cell A in one of the sheets referencing another cell B in another sheet. Links are set to updat autmatically. Everytime I change the value in Cell B, Cell A reflect the change as expected.

However, if I change the value of Cell B from out side excel (using Java POI apache api) then Cell A does not change. I open the sheet and Cell A still reflects the old value. If I do a recaluclate, same thing.

Any idea how to make this value update/change to reflect the new cell?

Please help

Snake
  • 14,228
  • 27
  • 117
  • 250
  • 2
    http://stackoverflow.com/questions/17929327/excel-formula-not-updating-cell?rq=1 might help. – ZAT Nov 08 '14 at 09:56
  • Yes it did.... Thank you so much. I searched alot and didnt get that thread. Put it as an answer and I will accept it – Snake Nov 09 '14 at 04:04
  • see posted answer. Felt like copy paste job from others answer though. – ZAT Nov 10 '14 at 07:30

1 Answers1

4

way 1. You can re-evaluate all formula cells by:

XSSFFormulaEvaluator.evaluateAllFormulaCells(workbook)

or

HSSFFormulaEvaluator.evaluateAllFormulaCells(workbook)

way 2.

wb.getCreationHelper().createFormulaEvaluator().evaluateAll()

or

wb.setForceFormulaRecalculation(true)

Ref link:

SO link: Excel Formula Not Updating Cell

Apache link: http://poi.apache.org/spreadsheet/eval.html

Credit should goes to following users:

akokskis and eugen-constantin-dinca and nilamber

Other links:

Apache link:Interface FormulaEvaluator

Apache link:Class HSSFFormulaEvaluator

Apache link:Class XSSFFormulaEvaluator

SO link:apache-poi-evaluate-formula

Community
  • 1
  • 1
ZAT
  • 1,347
  • 7
  • 10