It would be helpful for us to diagnose the problem if you attach a workbook and sample code that demonstrate the behavior - ideally a failing junit test. 

Yegor

(In reply to comment #1)
> It would be helpful for us to diagnose the problem if you attach a workbook and
> sample code that demonstrate the behavior - ideally a failing junit test. 
> 
> Yegor

hi Yegor,

  this is output i have got on console.I am attaching  the Excel file
  in this excel file cell value at location [0,2] ( 0 base indexing) use formula
  SUM(BOB+JIM) gives right value 30.but at location [0,3] which uses formula 
  SUM('named-cell-in-formula-test.xls'!BOB+'named-cell-in-formula-test.xls'!JIM)
  is not excuting. as it throws following error

java class code::


package test;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;

import org.apache.poi.hssf.record.formula.eval.BoolEval;
import org.apache.poi.hssf.record.formula.eval.NameXEval;
import org.apache.poi.hssf.record.formula.eval.NumberEval;
import org.apache.poi.hssf.record.formula.eval.StringEval;
import org.apache.poi.hssf.record.formula.eval.ValueEval;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.formula.IStabilityClassifier;
import org.apache.poi.ss.formula.eval.forked.ForkedEvaluator;


/**
 * Testing POI's use of Named Cells.
 * 
 * @author bsneade
 */
public class NamedCellTest {

	public static void main(final String[] args) {
		try {
			// load up the spreadsheet			
	String path="C:\\test\\named-cell-in-formula-test.xls";

			final HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(new File(path)));				
			//HSSFFormulaEvaluator.evaluateAllFormulaCells(wb);	
			HSSFSheet sheet1 = wb.getSheet("sheet1");
			String val2=getCellValue(wb, sheet1, 0, 2);
			System.out.println("[0,2]::"+val2);			
			String val4=getCellValue(wb, sheet1, 0, 3);
			System.out.println("[0,3]::"+val4);
			
		} catch (IOException e) {
			e.printStackTrace();
		}
		
	}
	
public static String getCellValue(HSSFWorkbook wb, HSSFSheet sheet,
			int rowNr, int colNr) {
		String value = null;
		ForkedEvaluator fEval = ForkedEvaluator.create(wb,
				IStabilityClassifier.TOTALLY_IMMUTABLE, null);
		HSSFRow row = sheet.getRow(rowNr);
		if (row != null) {
			HSSFCell cell = row.getCell(colNr);
			
	     if (cell != null) {
				switch (cell.getCellType()) {

		case HSSFCell.CELL_TYPE_STRING:
			value = cell.getRichStringCellValue().getString();
					break;
		case HSSFCell.CELL_TYPE_NUMERIC:
			value = Double.toString(cell.getNumericCellValue());
					break;
		case HSSFCell.CELL_TYPE_BOOLEAN:
			value = Boolean.toString(cell.getBooleanCellValue());
					break;
		case HSSFCell.CELL_TYPE_FORMULA:
					
ValueEval vEval=(ValueEval)fEval.evaluate(sheet.getSheetName(),rowNr,colNr);					
   if (vEval instanceof BoolEval) {
		value = ((BoolEval) vEval).getStringValue();
		} else if (vEval instanceof NumberEval) {
		  value = ((NumberEval) vEval).getStringValue();
		} else if (vEval instanceof StringEval) {
		  value = ((StringEval) vEval).getStringValue();
					}				
					break;					
				    default:
				}
			}
		}
		return value;
	}
	
}




[0,2]::30

Exception in thread "main" java.lang.RuntimeException: Unexpected arg eval type (org.apache.poi.hssf.record.formula.eval.NameXEval)
	at org.apache.poi.hssf.record.formula.eval.OperandResolver.coerceValueToDouble(OperandResolver.java:218)
	at org.apache.poi.hssf.record.formula.eval.TwoOperandNumericOperation.singleOperandEvaluate(TwoOperandNumericOperation.java:30)
	at org.apache.poi.hssf.record.formula.eval.TwoOperandNumericOperation.evaluate(TwoOperandNumericOperation.java:35)
	at org.apache.poi.hssf.record.formula.functions.Fixed2ArgFunction.evaluate(Fixed2ArgFunction.java:33)
	at org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:119)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:437)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:260)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:206)
	at org.apache.poi.ss.formula.eval.forked.ForkedEvaluator.evaluate(ForkedEvaluator.java:119)
	at test.NamedCellTest.getCellValue(NamedCellTest.java:68)
	at test.NamedCellTest.main(NamedCellTest.java:37)


Thanks
Ranvijay

Created attachment 25797 [details]
excel file for

Thanks for the additional information. 

I confirmed the problem and added failing unit test. Please wait, I hope it will be fixed soon.

Yegor

(In reply to comment #4)
> Thanks for the additional information. 
> 
> I confirmed the problem and added failing unit test. Please wait, I hope it
> will be fixed soon.
> 
> Yegor

hi Yegor,
            Is there any updates regarding my issue.


Thanks
Ranviyay

Name evaluation across workbooks is not supported by POI. Josh Micich, the developer who wrote most of the evaluation code confirmed that and and I have to resolve this bug report as "wontfix". 

The syntax SUM('file1.xls'!BOB+'file2.xls'!JIM) means that BOB and JIM are  external names and evaluator needs to open file1.xls and file2.xls to evaluate them and this is not supported.  

Yegor

(In reply to comment #6)
> Name evaluation across workbooks is not supported by POI. Josh Micich, the
> developer who wrote most of the evaluation code confirmed that and and I have
> to resolve this bug report as "wontfix". 
> 
> The syntax SUM('file1.xls'!BOB+'file2.xls'!JIM) means that BOB and JIM are 
> external names and evaluator needs to open file1.xls and file2.xls to evaluate
> them and this is not supported.  
> 
> Yegor

hi Yegor,
          Thanks for reply,
          I understand that external names are not supported by POI as you
          mentioned.but same error also occur in the same .xls file for two
          different sheets.
              lets say two sheets are sheet1 and sheet2 and excel file is
          file1.xls.lets there are two named defined name1 and name2 in sheet1

          now when i am trying to use SUM('sheet1'!name1+'sheet1'!name2)
          in sheet2 for the same excel file file1.xls,then same exception
          which i mentioned previously comes.
        
          Can You plz, confirmed me that this is supported by POI or not

Thanks
Ranvijay

I will tell for sure if you attach a sample file and java code demonstrating that evaluation of SUM('sheet1'!name1+'sheet1'!name2) does not work. 

Yegor

> hi Yegor,
>           Thanks for reply,
>           I understand that external names are not supported by POI as you
>           mentioned.but same error also occur in the same .xls file for two
>           different sheets.
>               lets say two sheets are sheet1 and sheet2 and excel file is
>           file1.xls.lets there are two named defined name1 and name2 in sheet1
> 
>           now when i am trying to use SUM('sheet1'!name1+'sheet1'!name2)
>           in sheet2 for the same excel file file1.xls,then same exception
>           which i mentioned previously comes.
> 
>           Can You plz, confirmed me that this is supported by POI or not
> 
> Thanks
> Ranvijay