problem while implementing dependent list in HSSF

[Nawanit, Niraj]

Hi all,

I am trying to implement dependent list on a column for XLS format and XLSX 
format. For this I have to use reference to another cell in same row inside the 
formula. I am using apache POI 3.7.

I am using formula as such: "=INDIRECT(UPPER($A7))" for list for 7th row 
onwards in 3rd column. I am facing weird problem. For HSSF implementation, in 
Office 2003 and office 2007, for every row, formula refers $A$7. In Office 2010 
I have not tested as of now. I want A8 to be referred for 8th row and A9 to be 
referred for 9th row and so on. In XSSF implementation, this works quite well.

The same problem I am facing for conditional formatting too.

Can someone please help? I have provided my implementation below.

Thanks in advance!
Niraj

import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.util.CellRangeAddressList;

import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.xssf.usermodel.XSSFDataValidationConstraint;

public static void addDropdown(Sheet sheet, String formula, int column, boolean 
isXSSF) {
CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(7, 500, 
column, column);
DataValidationConstraint dvConstraint;
If (isXSSF) {
dvConstraint  = new 
XSSFDataValidationConstraint(DataValidationConstraint.ValidationType.LIST, 
formula);
} else {
dvConstraint = DVConstraint.createFormulaListConstraint(formula);
}
  DataValidation dataValidation = 
sheet.getDataValidationHelper().createValidation(dvConstraint, 
cellRangeAddressList);
  dataValidation.setShowErrorBox(true);
}

public static void main(String[] args) {
Sheet sheet = … // comes from somewhere
addDropdown(sheet, "=INDIRECT(UPPER($A7))", 2, false); // for hssf
// addDropdown(sheet, "=INDIRECT(UPPER($A7))", 2, true); // for xssf
}