SetCellFormula with reference to another sheet

May 7, 2010 at 8:31 PM

I am trying to use SetCellFormula on several sheets with a reference to a single sheet with a template(not creating any of these sheets with NPOI).

For each sheet trying to reference MySheet I use:

VLOOKUP("StringValue",MySheet!$A$2:$B$700,2,FALSE)

The generated Excel file will have a REF# in place of the sheet.

VLOOKUP("StringValue",#REF!$A$2:$B$700,2,FALSE)

If I set the cell to a string value, open up the Excel and run the formula it works fine.

=VLOOKUP("StringValue",MySheet!$A$2:$B$700,2,FALSE) //Added to cell by formula not value

If I use INDIRECT ( INDIRECT('C1')) in the SetCellFormula with that reference to MySheet!$A$2:$B$700 the formula works fine when the file is generated.

So can another sheet be used directly in references with SetCellFormula or can SetCellFormula only directly reference cells on the current sheet?

Thanks.

 

May 21, 2010 at 2:03 AM
If you refresh your sheet after you open it, does the #REF... go away? If so, you should set this on your HSSFWorksheet like this "sheet.ForceFormulaRecalculation = true;". I've just tested coping/creating VLOOKUPS to a second sheet in my workbook with NPOI and I don't get any errors.