The formula document on the HSSF website instructed to report all occurrences of #VALUE! when attempting to programmatically assign a formula to a cell, so here goes: IF(A1 > 5, "big number", "small number") This formula works, and displays correctly when opened in Excel. #VALUE! is only displayed if the outer IF branches to an inner IF. For example: > IF(A1 > 5, "big number", IF(A1 < 2, "really small number", "small number")) #VALUE! is displayed if "A1 > 5" evaluates to false, and branches to the "else" argument, where there's a nested IF. If "A1 > 5" evaluates to true, the formula displays correctly, because there's no nested if in the "true" argument. #VALUE! is always displayed if both the true and false arguments contain nested IF's. -The cell displays #VALUE! -but if I type it in directly in Excel, the formula works -also, clicking in the formula bar of POI-generated formula, then hitting Enter, fixes the formula Thanks, -Sasha Borodin

Having AND in the eval part of a If in a formula results in #Value err too. If user goes to the output cell and presses F2 in Excel then presses enter ( makes Excel re evaluate it) the formula shows up ok. Using Calc now in Excel menu does not work Automated work around - placing a loop macro that touches all formula . 'In workbook Private Sub Workbook_Open() If Sheets(1).Range("iv1001").Value = "0" Then Sheets(1).Range("iv1001").Value = "1" Sheets(1).touchFormlas End If End Sub 'in sheet Sub touchFormlas() On Local Error GoTo errH Dim wkBk As Workbook Dim sht As Worksheet Dim i As Long Dim colFrom As Long Dim colTo As Long Dim rwFrm As Long Dim rwTo As Long Set wkBk = Workbooks(ActiveSheet.Cells(1, 2).Value) Set sht = wkBk.Sheets(ActiveSheet.Cells(2, 2).Value) rwFrm = ActiveSheet.Cells(3, 2).Value rwTo = ActiveSheet.Cells(4, 2).Value colFrom = ActiveSheet.Cells(5, 2).Value colTo = ActiveSheet.Cells(6, 2).Value If rwTo < 0 Then rwTo = rwFrm Do While Len(sht.Cells(rwTo, colFrom).Value) > 0 rwTo = rwTo + 1 Loop End If 'touch all cells formulas - workaround for #value POI bug 'run this macro in workbook open ( keep a flag in cell BI5000 so it does not run everytime u open - only first time) For i = rwFrm To rwTo For j = colFrom To colTo If sht.Cells(i, j).Formula <> "" Then z = sht.Cells(i, j).Formula sht.Cells(i, j).Formula = "" sht.Cells(i, j).Value = "=3" sht.Cells(i, j).Formula = z 'sht.Cells(i, j).Formula End If 'sht.Cells(i, j).Refresh Next Next Err.Clear If Err.Number <> 0 Then errH: Debug.Print Err.Number, Err.Description Resume Next End If End Sub

egample of formula that has AND =IF(AND(BH3<>0,BH3<>"",BK3<>""),BH3*BK3,0) workaround tried - nested if - that does not work in this case had another work around : =IF((BH4>BX4),BH4,0)*IF(BK4>BX4,BK4,0)

Retested OK in 3.1-beta1 Probably fixed in 3.0.1-alpha due to work on bug 24925 (2004-04-29, svn r353553)