Start to support formula parsing of sheet-specified named ranges, required for a full fix to bug #56737

git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1611753 13f79535-47bb-0310-9956-ffa450edef68

10 files changed, 192 insertions, 60 deletions

diff --git a/src/java/org/apache/poi/hssf/model/InternalWorkbook.java b/src/java/org/apache/poi/hssf/model/InternalWorkbook.java
index 9ecbdb97b7..0479a0b8e4 100644
--- a/src/java/org/apache/poi/hssf/model/InternalWorkbook.java
+++ b/src/java/org/apache/poi/hssf/model/InternalWorkbook.java
@@ -2358,12 +2358,13 @@ public final class InternalWorkbook {
     /**
      *
      * @param name the  name of an external function, typically a name of a UDF
+     * @param sheetRefIndex the sheet ref index, or -1 if not known
      * @param udf  locator of user-defiend functions to resolve names of VBA and Add-In functions
      * @return the external name or null
      */
-    public NameXPtg getNameXPtg(String name, UDFFinder udf) {
+    public NameXPtg getNameXPtg(String name, int sheetRefIndex, UDFFinder udf) {
         LinkTable lnk = getOrCreateLinkTable();
-        NameXPtg xptg = lnk.getNameXPtg(name);
+        NameXPtg xptg = lnk.getNameXPtg(name, sheetRefIndex);
 
         if(xptg == null && udf.findFunction(name) != null) {
             // the name was not found in the list of external names
@@ -2372,6 +2373,9 @@ public final class InternalWorkbook {
         }
         return xptg;
     }
+    public NameXPtg getNameXPtg(String name, UDFFinder udf) {
+        return getNameXPtg(name, -1, udf);
+    }
 
     /**
      * Check if the cloned sheet has drawings. If yes, then allocate a new drawing group ID and
diff --git a/src/java/org/apache/poi/hssf/model/LinkTable.java b/src/java/org/apache/poi/hssf/model/LinkTable.java
index 932096f72c..3b04d0d843 100644
--- a/src/java/org/apache/poi/hssf/model/LinkTable.java
+++ b/src/java/org/apache/poi/hssf/model/LinkTable.java
@@ -509,17 +509,27 @@ final class LinkTable {
       return _externalBookBlocks[extBookIndex].getNameIx(definedNameIndex);
 	}
 
-	public NameXPtg getNameXPtg(String name) {
+	/**
+	 * Finds the external name definition for the given name,
+	 *  optionally restricted by externsheet index, and returns
+	 *  (if found) as a NameXPtg.
+	 * @param sheetRefIndex The Extern Sheet Index to look for, or -1 if any
+	 */
+	public NameXPtg getNameXPtg(String name, int sheetRefIndex) {
 		// first find any external book block that contains the name:
 		for (int i = 0; i < _externalBookBlocks.length; i++) {
 			int definedNameIndex = _externalBookBlocks[i].getIndexOfName(name);
 			if (definedNameIndex < 0) {
 				continue;
 			}
-			// found it.
-			int sheetRefIndex = findRefIndexFromExtBookIndex(i);
-			if (sheetRefIndex >= 0) {
-				return new NameXPtg(sheetRefIndex, definedNameIndex);
+			
+			// Found one
+			int thisSheetRefIndex = findRefIndexFromExtBookIndex(i);
+			if (thisSheetRefIndex >= 0) {
+			    // Check for the sheet index match, if requested
+			    if (sheetRefIndex == -1 || thisSheetRefIndex == sheetRefIndex) {
+			        return new NameXPtg(thisSheetRefIndex, definedNameIndex);
+			    }
 			}
 		}
 		return null;
diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFEvaluationWorkbook.java b/src/java/org/apache/poi/hssf/usermodel/HSSFEvaluationWorkbook.java
index 070c75a397..d1b80932a6 100644
--- a/src/java/org/apache/poi/hssf/usermodel/HSSFEvaluationWorkbook.java
+++ b/src/java/org/apache/poi/hssf/usermodel/HSSFEvaluationWorkbook.java
@@ -21,9 +21,6 @@ import org.apache.poi.hssf.model.HSSFFormulaParser;
 import org.apache.poi.hssf.model.InternalWorkbook;
 import org.apache.poi.hssf.record.NameRecord;
 import org.apache.poi.hssf.record.aggregates.FormulaRecordAggregate;
-import org.apache.poi.ss.formula.ptg.NamePtg;
-import org.apache.poi.ss.formula.ptg.NameXPtg;
-import org.apache.poi.ss.formula.ptg.Ptg;
 import org.apache.poi.ss.SpreadsheetVersion;
 import org.apache.poi.ss.formula.EvaluationCell;
 import org.apache.poi.ss.formula.EvaluationName;
@@ -33,6 +30,9 @@ import org.apache.poi.ss.formula.FormulaParseException;
 import org.apache.poi.ss.formula.FormulaParsingWorkbook;
 import org.apache.poi.ss.formula.FormulaRenderingWorkbook;
 import org.apache.poi.ss.formula.FormulaType;
+import org.apache.poi.ss.formula.ptg.NamePtg;
+import org.apache.poi.ss.formula.ptg.NameXPtg;
+import org.apache.poi.ss.formula.ptg.Ptg;
 import org.apache.poi.ss.formula.udf.UDFFinder;
 import org.apache.poi.util.POILogFactory;
 import org.apache.poi.util.POILogger;
@@ -65,8 +65,8 @@ public final class HSSFEvaluationWorkbook implements FormulaRenderingWorkbook, E
 		return _iBook.getExternalSheetIndex(workbookName, sheetName);
 	}
 
-	public NameXPtg getNameXPtg(String name) {
-        return _iBook.getNameXPtg(name, _uBook.getUDFFinder());
+	public NameXPtg getNameXPtg(String name, int sheetRefIndex) {
+        return _iBook.getNameXPtg(name, sheetRefIndex, _uBook.getUDFFinder());
 	}
 
 	/**
diff --git a/src/java/org/apache/poi/ss/formula/FormulaParser.java b/src/java/org/apache/poi/ss/formula/FormulaParser.java
index 410a2b74f3..c8a5ecc553 100644
--- a/src/java/org/apache/poi/ss/formula/FormulaParser.java
+++ b/src/java/org/apache/poi/ss/formula/FormulaParser.java
@@ -21,12 +21,52 @@ import java.util.ArrayList;
 import java.util.List;
 import java.util.regex.Pattern;
 
+import org.apache.poi.ss.SpreadsheetVersion;
 import org.apache.poi.ss.formula.constant.ErrorConstant;
-import org.apache.poi.ss.formula.ptg.*;
 import org.apache.poi.ss.formula.function.FunctionMetadata;
 import org.apache.poi.ss.formula.function.FunctionMetadataRegistry;
+import org.apache.poi.ss.formula.ptg.AbstractFunctionPtg;
+import org.apache.poi.ss.formula.ptg.AddPtg;
+import org.apache.poi.ss.formula.ptg.Area3DPtg;
+import org.apache.poi.ss.formula.ptg.AreaPtg;
+import org.apache.poi.ss.formula.ptg.ArrayPtg;
+import org.apache.poi.ss.formula.ptg.AttrPtg;
+import org.apache.poi.ss.formula.ptg.BoolPtg;
+import org.apache.poi.ss.formula.ptg.ConcatPtg;
+import org.apache.poi.ss.formula.ptg.DividePtg;
+import org.apache.poi.ss.formula.ptg.EqualPtg;
+import org.apache.poi.ss.formula.ptg.ErrPtg;
+import org.apache.poi.ss.formula.ptg.FuncPtg;
+import org.apache.poi.ss.formula.ptg.FuncVarPtg;
+import org.apache.poi.ss.formula.ptg.GreaterEqualPtg;
+import org.apache.poi.ss.formula.ptg.GreaterThanPtg;
+import org.apache.poi.ss.formula.ptg.IntPtg;
+import org.apache.poi.ss.formula.ptg.LessEqualPtg;
+import org.apache.poi.ss.formula.ptg.LessThanPtg;
+import org.apache.poi.ss.formula.ptg.MemAreaPtg;
+import org.apache.poi.ss.formula.ptg.MemFuncPtg;
+import org.apache.poi.ss.formula.ptg.MissingArgPtg;
+import org.apache.poi.ss.formula.ptg.MultiplyPtg;
+import org.apache.poi.ss.formula.ptg.NamePtg;
+import org.apache.poi.ss.formula.ptg.NameXPtg;
+import org.apache.poi.ss.formula.ptg.NotEqualPtg;
+import org.apache.poi.ss.formula.ptg.NumberPtg;
+import org.apache.poi.ss.formula.ptg.OperandPtg;
+import org.apache.poi.ss.formula.ptg.OperationPtg;
+import org.apache.poi.ss.formula.ptg.ParenthesisPtg;
+import org.apache.poi.ss.formula.ptg.PercentPtg;
+import org.apache.poi.ss.formula.ptg.PowerPtg;
+import org.apache.poi.ss.formula.ptg.Ptg;
+import org.apache.poi.ss.formula.ptg.RangePtg;
+import org.apache.poi.ss.formula.ptg.Ref3DPtg;
+import org.apache.poi.ss.formula.ptg.RefPtg;
+import org.apache.poi.ss.formula.ptg.StringPtg;
+import org.apache.poi.ss.formula.ptg.SubtractPtg;
+import org.apache.poi.ss.formula.ptg.UnaryMinusPtg;
+import org.apache.poi.ss.formula.ptg.UnaryPlusPtg;
+import org.apache.poi.ss.formula.ptg.UnionPtg;
+import org.apache.poi.ss.formula.ptg.ValueOperatorPtg;
 import org.apache.poi.ss.usermodel.ErrorConstants;
-import org.apache.poi.ss.SpreadsheetVersion;
 import org.apache.poi.ss.util.AreaReference;
 import org.apache.poi.ss.util.CellReference;
 import org.apache.poi.ss.util.CellReference.NameType;
@@ -97,18 +137,26 @@ public final class FormulaParser {
 		public Identifier getSheetIdentifier() {
 			return _sheetIdentifier;
 		}
+		private void asFormulaString(StringBuffer sb) {
+            if (_bookName != null) {
+                sb.append(" [").append(_sheetIdentifier.getName()).append("]");
+            }
+            if (_sheetIdentifier.isQuoted()) {
+                sb.append("'").append(_sheetIdentifier.getName()).append("'");
+            } else {
+                sb.append(_sheetIdentifier.getName());
+            }
+		}
+		public String asFormulaString() {
+		    StringBuffer sb = new StringBuffer(32);
+		    asFormulaString(sb);
+		    return sb.toString();
+		}
 		public String toString() {
 			StringBuffer sb = new StringBuffer(64);
 			sb.append(getClass().getName());
 			sb.append(" [");
-			if (_bookName != null) {
-				sb.append(" [").append(_sheetIdentifier.getName()).append("]");
-			}
-			if (_sheetIdentifier.isQuoted()) {
-				sb.append("'").append(_sheetIdentifier.getName()).append("'");
-			} else {
-				sb.append(_sheetIdentifier.getName());
-			}
+			asFormulaString(sb);
 			sb.append("]");
 			return sb.toString();
 		}
@@ -398,6 +446,8 @@ public final class FormulaParser {
 	 *   'my sheet'!A1
 	 *   .my.sheet!A1
 	 *   my.named..range.
+	 *   'my sheet'!my.named.range
+	 *   .my.sheet!my.named.range
 	 *   foo.bar(123.456, "abc")
 	 *   123.456
 	 *   "abc"
@@ -420,10 +470,21 @@ public final class FormulaParser {
 		if (part1 == null) {
 			if (sheetIden != null) {
                 if(look == '#'){  // error ref like MySheet!#REF!
-                    return new ParseNode(ErrPtg.valueOf(parseErrorLiteral()));    
+                    return new ParseNode(ErrPtg.valueOf(parseErrorLiteral()));  
                 } else {
-                    throw new FormulaParseException("Cell reference expected after sheet name at index "
-                            + _pointer + ".");
+                    // Is it a named range?
+                    String name = parseAsName();
+                    if (name.length() == 0) {
+                        throw new FormulaParseException("Cell reference or Named Range "
+                                + "expected after sheet name at index " + _pointer + ".");
+                    }
+                    int extIx = getSheetExtIx(sheetIden);
+                    NameXPtg nameXPtg = _book.getNameXPtg(name, extIx);
+                    if (nameXPtg == null) {
+                        throw new FormulaParseException("Specified name '" + name +
+                                "' for sheet " + sheetIden.asFormulaString() + " not found");
+                    }
+                    return new ParseNode(nameXPtg);
                 }
 			}
 			return parseNonRange(savePointer);
@@ -540,20 +601,11 @@ public final class FormulaParser {
 		if (look == '"') {
 			return new ParseNode(new StringPtg(parseStringLiteral()));
 		}
+		
 		// from now on we can only be dealing with non-quoted identifiers
 		// which will either be named ranges or functions
-		StringBuilder sb = new StringBuilder();
+		String name = parseAsName();
 
-		// defined names may begin with a letter or underscore
-		if (!Character.isLetter(look) && look != '_') {
-			throw expected("number, string, or defined name");
-		}
-		while (isValidDefinedNameChar(look)) {
-			sb.append(look);
-			GetChar();
-		}
-		SkipWhite();
-		String name = sb.toString();
 		if (look == '(') {
 			return function(name);
 		}
@@ -576,6 +628,22 @@ public final class FormulaParser {
 		throw new FormulaParseException("Specified name '"
 				+ name + "' is not a range as expected.");
 	}
+	
+	private String parseAsName() {
+        StringBuilder sb = new StringBuilder();
+
+        // defined names may begin with a letter or underscore
+        if (!Character.isLetter(look) && look != '_') {
+            throw expected("number, string, or defined name");
+        }
+        while (isValidDefinedNameChar(look)) {
+            sb.append(look);
+            GetChar();
+        }
+        SkipWhite();
+        
+        return sb.toString();
+	}
 
 	/**
 	 *
@@ -594,6 +662,21 @@ public final class FormulaParser {
 		}
 		return false;
 	}
+	
+	private int getSheetExtIx(SheetIdentifier sheetIden) {
+        int extIx;
+        if (sheetIden == null) {
+            extIx = Integer.MIN_VALUE;
+        } else {
+            String sName = sheetIden.getSheetIdentifier().getName();
+            if (sheetIden.getBookName() == null) {
+                extIx = _book.getExternalSheetIndex(sName);
+            } else {
+                extIx = _book.getExternalSheetIndex(sheetIden.getBookName(), sName);
+            }
+        }
+        return extIx;
+	}
 
 	/**
 	 *
@@ -603,18 +686,8 @@ public final class FormulaParser {
 	 */
 	private ParseNode createAreaRefParseNode(SheetIdentifier sheetIden, SimpleRangePart part1,
 			SimpleRangePart part2) throws FormulaParseException {
+		int extIx = getSheetExtIx(sheetIden);
 
-		int extIx;
-		if (sheetIden == null) {
-			extIx = Integer.MIN_VALUE;
-		} else {
-			String sName = sheetIden.getSheetIdentifier().getName();
-			if (sheetIden.getBookName() == null) {
-				extIx = _book.getExternalSheetIndex(sName);
-			} else {
-				extIx = _book.getExternalSheetIndex(sheetIden.getBookName(), sName);
-			}
-		}
 		Ptg ptg;
 		if (part2 == null) {
 			CellReference cr = part1.getCellReference();
@@ -914,8 +987,7 @@ public final class FormulaParser {
 			}
 			EvaluationName hName = _book.getName(name, _sheetIndex);
 			if (hName == null) {
-
-				nameToken = _book.getNameXPtg(name);
+				nameToken = _book.getNameXPtg(name, -1);
 				if (nameToken == null) {
 					throw new FormulaParseException("Name '" + name
 							+ "' is completely unknown in the current workbook");
diff --git a/src/java/org/apache/poi/ss/formula/FormulaParsingWorkbook.java b/src/java/org/apache/poi/ss/formula/FormulaParsingWorkbook.java
index 5bafa57e54..f5b52237f7 100644
--- a/src/java/org/apache/poi/ss/formula/FormulaParsingWorkbook.java
+++ b/src/java/org/apache/poi/ss/formula/FormulaParsingWorkbook.java
@@ -17,8 +17,8 @@
 
 package org.apache.poi.ss.formula;
 
-import org.apache.poi.ss.formula.ptg.NameXPtg;
 import org.apache.poi.ss.SpreadsheetVersion;
+import org.apache.poi.ss.formula.ptg.NameXPtg;
 
 /**
  * Abstracts a workbook for the purpose of formula parsing.<br/>
@@ -33,7 +33,7 @@ public interface FormulaParsingWorkbook {
 	 */
 	EvaluationName getName(String name, int sheetIndex);
 
-	NameXPtg getNameXPtg(String name);
+	NameXPtg getNameXPtg(String name, int sheetRefIndex);
 
 	/**
 	 * gets the externSheet index for a sheet from this workbook
diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationWorkbook.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationWorkbook.java
index f7ce4f9d18..660f2dc40c 100644
--- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationWorkbook.java
+++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationWorkbook.java
@@ -102,7 +102,7 @@ public final class XSSFEvaluationWorkbook implements FormulaRenderingWorkbook, E
 	   throw new RuntimeException("Not implemented yet");
 	}
 
-	public NameXPtg getNameXPtg(String name) {
+	public NameXPtg getNameXPtg(String name, int sheetRefIndex) {
         IndexedUDFFinder udfFinder = (IndexedUDFFinder)getUDFFinder();
         FreeRefFunction func = udfFinder.findFunction(name);
 		if(func == null) return null;
diff --git a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java
index 423220c66e..92090c0e7a 100644
--- a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java
+++ b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java
@@ -1551,7 +1551,7 @@ public final class TestXSSFBugs extends BaseTestBugzillaIssues {
         bug53798Work(wb, xlsOutput);
     }
 
-    @Ignore("Shifting rows is not yet implemented in XSSFSheet")
+    @Ignore("Shifting rows is not yet implemented in SXSSFSheet")
     @Test
     public void testBug53798XLSXStream() throws IOException {
         XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("53798_shiftNegative_TMPL.xlsx");
@@ -1660,8 +1660,8 @@ public final class TestXSSFBugs extends BaseTestBugzillaIssues {
      * org.apache.poi.ss.formula.FormulaParseException: Cell reference expected after sheet name at index 9
      * org.apache.poi.ss.formula.FormulaParseException: Parse error near char 0 '[' in specified formula '[0]!NR_Global_B2'. Expected number, string, or defined name 
      */
-    @Ignore
     @Test
+    @Ignore
     public void bug56737() throws IOException {
         Workbook wb = XSSFTestDataSamples.openSampleWorkbook("56737.xlsx");
         
@@ -1681,6 +1681,7 @@ public final class TestXSSFBugs extends BaseTestBugzillaIssues {
         Cell cRefWName = s.getRow(2).getCell(3);
         
         assertEquals("Defines!NR_To_A1", cRefSName.getCellFormula());
+        // TODO Why aren't we showing the real filename as Excel does?
         assertEquals("[0]!NR_Global_B2", cRefWName.getCellFormula());
         
         // Try to evaluate them
diff --git a/src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java b/src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java
index fa872e1e31..59faa04303 100644
--- a/src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java
+++ b/src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java
@@ -37,7 +37,40 @@ import org.apache.poi.ss.formula.FormulaParseException;
 import org.apache.poi.ss.formula.FormulaParser;
 import org.apache.poi.ss.formula.FormulaType;
 import org.apache.poi.ss.formula.constant.ErrorConstant;
-import org.apache.poi.ss.formula.ptg.*;
+import org.apache.poi.ss.formula.ptg.AbstractFunctionPtg;
+import org.apache.poi.ss.formula.ptg.AddPtg;
+import org.apache.poi.ss.formula.ptg.Area3DPtg;
+import org.apache.poi.ss.formula.ptg.AreaI;
+import org.apache.poi.ss.formula.ptg.AreaPtg;
+import org.apache.poi.ss.formula.ptg.AreaPtgBase;
+import org.apache.poi.ss.formula.ptg.ArrayPtg;
+import org.apache.poi.ss.formula.ptg.AttrPtg;
+import org.apache.poi.ss.formula.ptg.BoolPtg;
+import org.apache.poi.ss.formula.ptg.ConcatPtg;
+import org.apache.poi.ss.formula.ptg.DividePtg;
+import org.apache.poi.ss.formula.ptg.EqualPtg;
+import org.apache.poi.ss.formula.ptg.ErrPtg;
+import org.apache.poi.ss.formula.ptg.FuncPtg;
+import org.apache.poi.ss.formula.ptg.FuncVarPtg;
+import org.apache.poi.ss.formula.ptg.IntPtg;
+import org.apache.poi.ss.formula.ptg.MemAreaPtg;
+import org.apache.poi.ss.formula.ptg.MemFuncPtg;
+import org.apache.poi.ss.formula.ptg.MissingArgPtg;
+import org.apache.poi.ss.formula.ptg.MultiplyPtg;
+import org.apache.poi.ss.formula.ptg.NamePtg;
+import org.apache.poi.ss.formula.ptg.NumberPtg;
+import org.apache.poi.ss.formula.ptg.ParenthesisPtg;
+import org.apache.poi.ss.formula.ptg.PercentPtg;
+import org.apache.poi.ss.formula.ptg.PowerPtg;
+import org.apache.poi.ss.formula.ptg.Ptg;
+import org.apache.poi.ss.formula.ptg.RangePtg;
+import org.apache.poi.ss.formula.ptg.Ref3DPtg;
+import org.apache.poi.ss.formula.ptg.RefPtg;
+import org.apache.poi.ss.formula.ptg.StringPtg;
+import org.apache.poi.ss.formula.ptg.SubtractPtg;
+import org.apache.poi.ss.formula.ptg.UnaryMinusPtg;
+import org.apache.poi.ss.formula.ptg.UnaryPlusPtg;
+import org.apache.poi.ss.formula.ptg.UnionPtg;
 import org.apache.poi.ss.usermodel.BaseTestBugzillaIssues;
 import org.apache.poi.ss.usermodel.Name;
 import org.apache.poi.util.HexRead;
@@ -1175,7 +1208,9 @@ public final class TestFormulaParser extends TestCase {
 
 		confirmParseError(wb, "A1:ROUND(B1,1)", "The RHS of the range operator ':' at position 3 is not a proper reference.");
 
-		confirmParseError(wb, "Sheet1!Sheet1", "Cell reference expected after sheet name at index 8.");
+        confirmParseError(wb, "Sheet1!!!", "Parse error near char 7 '!' in specified formula 'Sheet1!!!'. Expected number, string, or defined name");
+        confirmParseError(wb, "Sheet1!.Name", "Parse error near char 7 '.' in specified formula 'Sheet1!.Name'. Expected number, string, or defined name");
+		confirmParseError(wb, "Sheet1!Sheet1", "Specified name 'Sheet1' for sheet Sheet1 not found");
 		confirmParseError(wb, "Sheet1!F:Sheet1!G", "'Sheet1!F' is not a proper reference.");
 		confirmParseError(wb, "Sheet1!F..foobar", "Complete area reference expected after sheet name at index 11.");
 		confirmParseError(wb, "Sheet1!A .. B", "Dotted range (full row or column) expression 'A .. B' must not contain whitespace.");
diff --git a/src/testcases/org/apache/poi/hssf/model/TestLinkTable.java b/src/testcases/org/apache/poi/hssf/model/TestLinkTable.java
index 48c81ea0bb..4f25a1d08c 100644
--- a/src/testcases/org/apache/poi/hssf/model/TestLinkTable.java
+++ b/src/testcases/org/apache/poi/hssf/model/TestLinkTable.java
@@ -211,13 +211,19 @@ public final class TestLinkTable extends TestCase {
         ExternSheetRecord extSheet = (ExternSheetRecord)wrl.get(3);
         assertEquals(0, extSheet.getNumOfRefs());
 
-        assertNull(tbl.getNameXPtg("ISODD"));
+        assertNull(tbl.getNameXPtg("ISODD", -1));
         assertEquals(5, wrl.getRecords().size()); //still have five records
 
         NameXPtg namex1 = tbl.addNameXPtg("ISODD");  // adds two new rercords
         assertEquals(0, namex1.getSheetRefIndex());
         assertEquals(0, namex1.getNameIndex());
-        assertEquals(namex1.toString(), tbl.getNameXPtg("ISODD").toString());
+        assertEquals(namex1.toString(), tbl.getNameXPtg("ISODD", -1).toString());
+        
+        // Can only find on the right sheet ref, if restricting
+        assertEquals(namex1.toString(), tbl.getNameXPtg("ISODD", 0).toString());
+        assertNull(tbl.getNameXPtg("ISODD", 1));
+        assertNull(tbl.getNameXPtg("ISODD", 2));
+        
         // assure they are in place:
         //    [BOFRecord]
         //    [CountryRecord]
@@ -241,11 +247,11 @@ public final class TestLinkTable extends TestCase {
         assertEquals(0, tbl.resolveNameXIx(namex1.getSheetRefIndex(), namex1.getNameIndex()));
         assertEquals("ISODD", tbl.resolveNameXText(namex1.getSheetRefIndex(), namex1.getNameIndex(), null));
 
-        assertNull(tbl.getNameXPtg("ISEVEN"));
+        assertNull(tbl.getNameXPtg("ISEVEN", -1));
         NameXPtg namex2 = tbl.addNameXPtg("ISEVEN");  // adds two new rercords
         assertEquals(0, namex2.getSheetRefIndex());
         assertEquals(1, namex2.getNameIndex());  // name index increased by one
-        assertEquals(namex2.toString(), tbl.getNameXPtg("ISEVEN").toString());
+        assertEquals(namex2.toString(), tbl.getNameXPtg("ISEVEN", -1).toString());
         assertEquals(8, wrl.getRecords().size());
         // assure they are in place:
         //    [BOFRecord]
diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java b/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java
index 9421b56348..4607407eb2 100644
--- a/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java
+++ b/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java
@@ -2678,5 +2678,9 @@ public final class TestBugs extends BaseTestBugzillaIssues {
         
         // Try to evaluate everything
         eval.evaluateAll();
+        
+        // Try to set the same kinds of formula elsewhere
+        Cell newF = s.getRow(0).createCell(10, Cell.CELL_TYPE_FORMULA);
+        newF.setCellFormula("Defines!NR_To_A1");
     }
 }