Bugzilla 47721 - Added implementation for INDIRECT()

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

15 files changed, 601 insertions, 187 deletions

diff --git a/src/java/org/apache/poi/hssf/record/formula/atp/AnalysisToolPak.java b/src/java/org/apache/poi/hssf/record/formula/atp/AnalysisToolPak.java
index af56041521..24423d04a0 100644
--- a/src/java/org/apache/poi/hssf/record/formula/atp/AnalysisToolPak.java
+++ b/src/java/org/apache/poi/hssf/record/formula/atp/AnalysisToolPak.java
@@ -22,7 +22,7 @@ import java.util.Map;
 
 import org.apache.poi.hssf.record.formula.eval.ValueEval;
 import org.apache.poi.hssf.record.formula.functions.FreeRefFunction;
-import org.apache.poi.ss.formula.EvaluationWorkbook;
+import org.apache.poi.ss.formula.OperationEvaluationContext;
 import org.apache.poi.ss.formula.eval.NotImplementedException;
 
 public final class AnalysisToolPak {
@@ -34,8 +34,7 @@ public final class AnalysisToolPak {
 			_functionName = functionName;
 		}
 
-		public ValueEval evaluate(ValueEval[] args, EvaluationWorkbook workbook, int srcCellSheet,
-				int srcCellRow, int srcCellCol) {
+		public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) {
 			throw new NotImplementedException(_functionName);
 		}
 	};
diff --git a/src/java/org/apache/poi/hssf/record/formula/atp/ParityFunction.java b/src/java/org/apache/poi/hssf/record/formula/atp/ParityFunction.java
index 9235d15b1c..5ab9a70b7b 100644
--- a/src/java/org/apache/poi/hssf/record/formula/atp/ParityFunction.java
+++ b/src/java/org/apache/poi/hssf/record/formula/atp/ParityFunction.java
@@ -23,7 +23,7 @@ import org.apache.poi.hssf.record.formula.eval.EvaluationException;
 import org.apache.poi.hssf.record.formula.eval.OperandResolver;
 import org.apache.poi.hssf.record.formula.eval.ValueEval;
 import org.apache.poi.hssf.record.formula.functions.FreeRefFunction;
-import org.apache.poi.ss.formula.EvaluationWorkbook;
+import org.apache.poi.ss.formula.OperationEvaluationContext;
 /**
  * Implementation of Excel 'Analysis ToolPak' function ISEVEN() ISODD()<br/>
  *
@@ -39,15 +39,14 @@ final class ParityFunction implements FreeRefFunction {
 		_desiredParity = desiredParity;
 	}
 
-	public ValueEval evaluate(ValueEval[] args, EvaluationWorkbook workbook, int srcCellSheet, int srcCellRow,
-			int srcCellCol) {
+	public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) {
 		if (args.length != 1) {
 			return ErrorEval.VALUE_INVALID;
 		}
 
 		int val;
 		try {
-			val = evaluateArgParity(args[0], srcCellRow, srcCellCol);
+			val = evaluateArgParity(args[0], ec.getRowIndex(), ec.getColumnIndex());
 		} catch (EvaluationException e) {
 			return e.getErrorEval();
 		}
diff --git a/src/java/org/apache/poi/hssf/record/formula/atp/YearFrac.java b/src/java/org/apache/poi/hssf/record/formula/atp/YearFrac.java
index 4b2ba89071..d36b05a36b 100644
--- a/src/java/org/apache/poi/hssf/record/formula/atp/YearFrac.java
+++ b/src/java/org/apache/poi/hssf/record/formula/atp/YearFrac.java
@@ -28,7 +28,7 @@ import org.apache.poi.hssf.record.formula.eval.OperandResolver;
 import org.apache.poi.hssf.record.formula.eval.StringEval;
 import org.apache.poi.hssf.record.formula.eval.ValueEval;
 import org.apache.poi.hssf.record.formula.functions.FreeRefFunction;
-import org.apache.poi.ss.formula.EvaluationWorkbook;
+import org.apache.poi.ss.formula.OperationEvaluationContext;
 import org.apache.poi.ss.usermodel.DateUtil;
 /**
  * Implementation of Excel 'Analysis ToolPak' function YEARFRAC()<br/>
@@ -58,9 +58,9 @@ final class YearFrac implements FreeRefFunction {
 		// enforce singleton
 	}
 
-	public ValueEval evaluate(ValueEval[] args, EvaluationWorkbook workbook, int srcCellSheet, int srcCellRow,
-			int srcCellCol) {
-
+	public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) {
+		int srcCellRow = ec.getRowIndex();
+		int srcCellCol = ec.getColumnIndex();
 		double result;
 		try {
 			int basis = 0; // default
diff --git a/src/java/org/apache/poi/hssf/record/formula/eval/FunctionEval.java b/src/java/org/apache/poi/hssf/record/formula/eval/FunctionEval.java
index 5d04de0fc3..19476c9f13 100644
--- a/src/java/org/apache/poi/hssf/record/formula/eval/FunctionEval.java
+++ b/src/java/org/apache/poi/hssf/record/formula/eval/FunctionEval.java
@@ -20,63 +20,8 @@ package org.apache.poi.hssf.record.formula.eval;
 import org.apache.poi.hssf.record.formula.AbstractFunctionPtg;
 import org.apache.poi.hssf.record.formula.function.FunctionMetadata;
 import org.apache.poi.hssf.record.formula.function.FunctionMetadataRegistry;
-import org.apache.poi.hssf.record.formula.functions.AggregateFunction;
-import org.apache.poi.hssf.record.formula.functions.And;
-import org.apache.poi.hssf.record.formula.functions.CalendarFieldFunction;
-import org.apache.poi.hssf.record.formula.functions.Choose;
-import org.apache.poi.hssf.record.formula.functions.Column;
-import org.apache.poi.hssf.record.formula.functions.Columns;
-import org.apache.poi.hssf.record.formula.functions.Count;
-import org.apache.poi.hssf.record.formula.functions.Counta;
-import org.apache.poi.hssf.record.formula.functions.Countif;
-import org.apache.poi.hssf.record.formula.functions.DateFunc;
-import org.apache.poi.hssf.record.formula.functions.Errortype;
-import org.apache.poi.hssf.record.formula.functions.Even;
-import org.apache.poi.hssf.record.formula.functions.False;
-import org.apache.poi.hssf.record.formula.functions.FinanceFunction;
-import org.apache.poi.hssf.record.formula.functions.Find;
-import org.apache.poi.hssf.record.formula.functions.FreeRefFunction;
-import org.apache.poi.hssf.record.formula.functions.Function;
-import org.apache.poi.hssf.record.formula.functions.Hlookup;
-import org.apache.poi.hssf.record.formula.functions.Hyperlink;
-import org.apache.poi.hssf.record.formula.functions.If;
-import org.apache.poi.hssf.record.formula.functions.Index;
-import org.apache.poi.hssf.record.formula.functions.Indirect;
-import org.apache.poi.hssf.record.formula.functions.IsError;
-import org.apache.poi.hssf.record.formula.functions.IsNa;
-import org.apache.poi.hssf.record.formula.functions.Isblank;
-import org.apache.poi.hssf.record.formula.functions.Isref;
-import org.apache.poi.hssf.record.formula.functions.LogicalFunction;
-import org.apache.poi.hssf.record.formula.functions.Lookup;
-import org.apache.poi.hssf.record.formula.functions.Match;
-import org.apache.poi.hssf.record.formula.functions.MinaMaxa;
-import org.apache.poi.hssf.record.formula.functions.Mode;
-import org.apache.poi.hssf.record.formula.functions.Na;
-import org.apache.poi.hssf.record.formula.functions.Not;
-import org.apache.poi.hssf.record.formula.functions.NotImplementedFunction;
-import org.apache.poi.hssf.record.formula.functions.Now;
-import org.apache.poi.hssf.record.formula.functions.NumericFunction;
-import org.apache.poi.hssf.record.formula.functions.Odd;
-import org.apache.poi.hssf.record.formula.functions.Offset;
-import org.apache.poi.hssf.record.formula.functions.Or;
-import org.apache.poi.hssf.record.formula.functions.Pi;
-import org.apache.poi.hssf.record.formula.functions.Rand;
-import org.apache.poi.hssf.record.formula.functions.Replace;
-import org.apache.poi.hssf.record.formula.functions.Row;
-import org.apache.poi.hssf.record.formula.functions.Rows;
-import org.apache.poi.hssf.record.formula.functions.Substitute;
-import org.apache.poi.hssf.record.formula.functions.Sumif;
-import org.apache.poi.hssf.record.formula.functions.Sumproduct;
-import org.apache.poi.hssf.record.formula.functions.Sumx2my2;
-import org.apache.poi.hssf.record.formula.functions.Sumx2py2;
-import org.apache.poi.hssf.record.formula.functions.Sumxmy2;
-import org.apache.poi.hssf.record.formula.functions.T;
-import org.apache.poi.hssf.record.formula.functions.TextFunction;
-import org.apache.poi.hssf.record.formula.functions.Time;
-import org.apache.poi.hssf.record.formula.functions.Today;
-import org.apache.poi.hssf.record.formula.functions.True;
-import org.apache.poi.hssf.record.formula.functions.Value;
-import org.apache.poi.hssf.record.formula.functions.Vlookup;
+import org.apache.poi.hssf.record.formula.functions.*;
+import org.apache.poi.ss.formula.OperationEvaluationContext;
 import org.apache.poi.ss.formula.eval.NotImplementedException;
 
 /**
@@ -99,26 +44,10 @@ public final class FunctionEval implements OperationEval {
 	// convenient access to namespace
 	private static final FunctionID ID = null;
 
-	protected static final Function[] functions = produceFunctions();
-
-
 	/**
-	 * @return <code>null</code> if specified function
+	 * Array elements corresponding to unimplemented functions are <code>null</code>
 	 */
-	private Function getFunction() {
-		short fidx = getFunctionIndex();
-		return functions[fidx];
-	}
-	public boolean isFreeRefFunction() {
-		return getFreeRefFunction() != null;
-	}
-	public FreeRefFunction getFreeRefFunction() {
-		switch (getFunctionIndex()) {
-			case FunctionID.INDIRECT: return Indirect.instance;
-			case FunctionID.EXTERNAL_FUNC: return UserDefinedFunction.instance;
-		}
-		return null;
-	}
+	protected static final Function[] functions = produceFunctions();
 
 	private static Function[] produceFunctions() {
 		Function[] retval = new Function[368];
@@ -299,19 +228,26 @@ public final class FunctionEval implements OperationEval {
 		_delegate = funcPtg;
 	}
 
-	public ValueEval evaluate(ValueEval[] operands, int srcRow, short srcCol) {
-		Function f = getFunction();
+	public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) {
+		int fidx = _delegate.getFunctionIndex();
+		// check for 'free ref' functions first
+		switch (fidx) {
+			case FunctionID.INDIRECT:
+				return Indirect.instance.evaluate(args, ec);
+			case FunctionID.EXTERNAL_FUNC:
+				return UserDefinedFunction.instance.evaluate(args, ec);
+		}
+		// else - must be plain function
+		Function f = functions[fidx];
 		if (f == null) {
-			throw new NotImplementedException("FuncIx=" + getFunctionIndex());
+			throw new NotImplementedException("FuncIx=" + fidx);
 		}
-		return f.evaluate(operands, srcRow, srcCol);
+		int srcCellRow = ec.getRowIndex();
+		int srcCellCol = ec.getColumnIndex();
+		return f.evaluate(args, srcCellRow, (short) srcCellCol);
 	}
 
 	public int getNumberOfOperands() {
 		return _delegate.getNumberOfOperands();
 	}
-
-	private short getFunctionIndex() {
-		return _delegate.getFunctionIndex();
-	}
 }
diff --git a/src/java/org/apache/poi/hssf/record/formula/eval/OperationEval.java b/src/java/org/apache/poi/hssf/record/formula/eval/OperationEval.java
index d66c314d7d..481caa0b6b 100644
--- a/src/java/org/apache/poi/hssf/record/formula/eval/OperationEval.java
+++ b/src/java/org/apache/poi/hssf/record/formula/eval/OperationEval.java
@@ -17,6 +17,8 @@
 
 package org.apache.poi.hssf.record.formula.eval;
 
+import org.apache.poi.ss.formula.OperationEvaluationContext;
+
 /**
  * Common interface for implementations of Excel formula operations.
  *
@@ -29,11 +31,10 @@ public interface OperationEval {
 	 * @param args the evaluated operation arguments. Elements of this array typically implement
 	 * {@link ValueEval}.  Empty values are represented with {@link BlankEval} or {@link
 	 * MissingArgEval}, never <code>null</code>.
-	 * @param srcRowIndex row index of the cell containing the formula under evaluation
-	 * @param srcColumnIndex column index of the cell containing the formula under evaluation
+	 * @param ec used to identify the current cell under evaluation, and potentially to
+	 * dynamically create references
 	 * @return The evaluated result, possibly an {@link ErrorEval}, never <code>null</code>.
 	 */
-	ValueEval evaluate(ValueEval[] args, int srcRowIndex, short srcColumnIndex);
-
+	ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec);
 	int getNumberOfOperands();
 }
diff --git a/src/java/org/apache/poi/hssf/record/formula/eval/UserDefinedFunction.java b/src/java/org/apache/poi/hssf/record/formula/eval/UserDefinedFunction.java
index 384b576eec..f12c96e59f 100755
--- a/src/java/org/apache/poi/hssf/record/formula/eval/UserDefinedFunction.java
+++ b/src/java/org/apache/poi/hssf/record/formula/eval/UserDefinedFunction.java
@@ -20,6 +20,7 @@ package org.apache.poi.hssf.record.formula.eval;
 import org.apache.poi.hssf.record.formula.atp.AnalysisToolPak;
 import org.apache.poi.hssf.record.formula.functions.FreeRefFunction;
 import org.apache.poi.ss.formula.EvaluationWorkbook;
+import org.apache.poi.ss.formula.OperationEvaluationContext;
 import org.apache.poi.ss.formula.eval.NotImplementedException;
 /**
  *
@@ -36,8 +37,7 @@ final class UserDefinedFunction implements FreeRefFunction {
 		// enforce singleton
 	}
 
-	public ValueEval evaluate(ValueEval[] args, EvaluationWorkbook workbook,
-			int srcCellSheet, int srcCellRow,int srcCellCol) {
+	public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) {
 
 		int nIncomingArgs = args.length;
 		if(nIncomingArgs < 1) {
@@ -49,7 +49,7 @@ final class UserDefinedFunction implements FreeRefFunction {
 		if (nameArg instanceof NameEval) {
 			targetFunc = findInternalUserDefinedFunction((NameEval) nameArg);
 		} else if (nameArg instanceof NameXEval) {
-			targetFunc = findExternalUserDefinedFunction(workbook, (NameXEval) nameArg);
+			targetFunc = findExternalUserDefinedFunction(ec.getWorkbook(), (NameXEval) nameArg);
 		} else {
 			throw new RuntimeException("First argument should be a NameEval, but got ("
 					+ nameArg.getClass().getName() + ")");
@@ -57,7 +57,7 @@ final class UserDefinedFunction implements FreeRefFunction {
 		int nOutGoingArgs = nIncomingArgs -1;
 		ValueEval[] outGoingArgs = new ValueEval[nOutGoingArgs];
 		System.arraycopy(args, 1, outGoingArgs, 0, nOutGoingArgs);
-		return targetFunc.evaluate(outGoingArgs, workbook, srcCellSheet, srcCellRow, srcCellCol);
+		return targetFunc.evaluate(outGoingArgs, ec);
 	}
 
 	private static FreeRefFunction findExternalUserDefinedFunction(EvaluationWorkbook workbook,
diff --git a/src/java/org/apache/poi/hssf/record/formula/functions/FreeRefFunction.java b/src/java/org/apache/poi/hssf/record/formula/functions/FreeRefFunction.java
index c806472068..119592101d 100755
--- a/src/java/org/apache/poi/hssf/record/formula/functions/FreeRefFunction.java
+++ b/src/java/org/apache/poi/hssf/record/formula/functions/FreeRefFunction.java
@@ -18,7 +18,7 @@
 package org.apache.poi.hssf.record.formula.functions;
 
 import org.apache.poi.hssf.record.formula.eval.ValueEval;
-import org.apache.poi.ss.formula.EvaluationWorkbook;
+import org.apache.poi.ss.formula.OperationEvaluationContext;
 
 
 /**
@@ -28,28 +28,24 @@ import org.apache.poi.ss.formula.EvaluationWorkbook;
  * argument.<br/>
  * Two important functions with this feature are <b>INDIRECT</b> and <b>OFFSET</b><p/>
  *
- * In POI, the <tt>HSSFFormulaEvaluator</tt> evaluates every cell in each reference argument before
- * calling the function.  This means that functions using fixed references do not need access to
- * the rest of the workbook to execute.  Hence the <tt>evaluate()</tt> method on the common
- * interface <tt>Function</tt> does not take a workbook parameter.<p>
+ * When POI evaluates formulas, each reference argument is capable of evaluating any cell inside
+ * its range.  Actually, even cells outside the reference range but on the same sheet can be
+ * evaluated.  This allows <b>OFFSET</b> to be implemented like most other functions - taking only
+ * the arguments, and source cell coordinates.
  *
- * This interface recognises the requirement of some functions to freely create and evaluate
- * references beyond those passed in as arguments.
+ * For the moment this interface only exists to serve the <b>INDIRECT</b> which can decode
+ * arbitrary text into cell references, and evaluate them..
  *
  * @author Josh Micich
  */
 public interface FreeRefFunction {
 	/**
-	 *
 	 * @param args the pre-evaluated arguments for this function. args is never <code>null</code>,
-	 * 		  nor are any of its elements.
-	 * @param srcCellSheet zero based sheet index of the cell containing the currently evaluating formula
-	 * @param srcCellRow zero based row index of the cell containing the currently evaluating formula
-	 * @param srcCellCol zero based column index of the cell containing the currently evaluating formula
-	 * @param workbook is the workbook containing the formula/cell being evaluated
+	 *             nor are any of its elements.
+	 * @param ec primarily used to identify the source cell containing the formula being evaluated.
+	 *             may also be used to dynamically create reference evals.
 	 * @return never <code>null</code>. Possibly an instance of <tt>ErrorEval</tt> in the case of
 	 * a specified Excel error (Exceptions are never thrown to represent Excel errors).
-	 *
 	 */
-	ValueEval evaluate(ValueEval[] args, EvaluationWorkbook workbook, int srcCellSheet, int srcCellRow, int srcCellCol);
+	ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec);
 }
diff --git a/src/java/org/apache/poi/hssf/record/formula/functions/Indirect.java b/src/java/org/apache/poi/hssf/record/formula/functions/Indirect.java
index d7b031d565..80d515ac14 100644
--- a/src/java/org/apache/poi/hssf/record/formula/functions/Indirect.java
+++ b/src/java/org/apache/poi/hssf/record/formula/functions/Indirect.java
@@ -17,9 +17,13 @@
 
 package org.apache.poi.hssf.record.formula.functions;
 
+import org.apache.poi.hssf.record.formula.eval.BlankEval;
+import org.apache.poi.hssf.record.formula.eval.ErrorEval;
+import org.apache.poi.hssf.record.formula.eval.EvaluationException;
+import org.apache.poi.hssf.record.formula.eval.MissingArgEval;
+import org.apache.poi.hssf.record.formula.eval.OperandResolver;
 import org.apache.poi.hssf.record.formula.eval.ValueEval;
-import org.apache.poi.ss.formula.EvaluationWorkbook;
-import org.apache.poi.ss.formula.eval.NotImplementedException;
+import org.apache.poi.ss.formula.OperationEvaluationContext;
 
 /**
  * Implementation for Excel function INDIRECT<p/>
@@ -29,11 +33,10 @@ import org.apache.poi.ss.formula.eval.NotImplementedException;
  * <b>Syntax</b>:</br>
  * <b>INDIRECT</b>(<b>ref_text</b>,isA1Style)<p/>
  *
- * <b>ref_text</b> a string representation of the desired reference as it would normally be written
- * in a cell formula.<br/>
- * <b>isA1Style</b> (default TRUE) specifies whether the ref_text should be interpreted as A1-style
- * or R1C1-style.
- *
+ * <b>ref_text</b> a string representation of the desired reference as it would
+ * normally be written in a cell formula.<br/>
+ * <b>isA1Style</b> (default TRUE) specifies whether the ref_text should be
+ * interpreted as A1-style or R1C1-style.
  *
  * @author Josh Micich
  */
@@ -45,8 +48,192 @@ public final class Indirect implements FreeRefFunction {
 		// enforce singleton
 	}
 
-	public ValueEval evaluate(ValueEval[] args, EvaluationWorkbook workbook, int srcCellSheet, int srcCellRow, int srcCellCol) {
-		// TODO - implement INDIRECT()
-		throw new NotImplementedException("INDIRECT");
+	public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) {
+		if (args.length < 1) {
+			return ErrorEval.VALUE_INVALID;
+		}
+
+		boolean isA1style;
+		String text;
+		try {
+			ValueEval ve = OperandResolver.getSingleValue(args[0], ec.getRowIndex(), ec
+					.getColumnIndex());
+			text = OperandResolver.coerceValueToString(ve);
+			switch (args.length) {
+				case 1:
+					isA1style = true;
+					break;
+				case 2:
+					isA1style = evaluateBooleanArg(args[1], ec);
+					break;
+				default:
+					return ErrorEval.VALUE_INVALID;
+			}
+		} catch (EvaluationException e) {
+			return e.getErrorEval();
+		}
+
+		return evaluateIndirect(ec, text, isA1style);
+	}
+
+	private static boolean evaluateBooleanArg(ValueEval arg, OperationEvaluationContext ec)
+			throws EvaluationException {
+		ValueEval ve = OperandResolver.getSingleValue(arg, ec.getRowIndex(), ec.getColumnIndex());
+
+		if (ve == BlankEval.INSTANCE || ve == MissingArgEval.instance) {
+			return false;
+		}
+		// numeric quantities follow standard boolean conversion rules
+		// for strings, only "TRUE" and "FALSE" (case insensitive) are valid
+		return OperandResolver.coerceValueToBoolean(ve, false).booleanValue();
+	}
+
+	private static ValueEval evaluateIndirect(OperationEvaluationContext ec, String text,
+			boolean isA1style) {
+		// Search backwards for '!' because sheet names can contain '!'
+		int plingPos = text.lastIndexOf('!');
+
+		String workbookName;
+		String sheetName;
+		String refText; // whitespace around this gets trimmed OK
+		if (plingPos < 0) {
+			workbookName = null;
+			sheetName = null;
+			refText = text;
+		} else {
+			String[] parts = parseWorkbookAndSheetName(text.subSequence(0, plingPos));
+			if (parts == null) {
+				return ErrorEval.REF_INVALID;
+			}
+			workbookName = parts[0];
+			sheetName = parts[1];
+			refText = text.substring(plingPos + 1);
+		}
+
+		String refStrPart1;
+		String refStrPart2;
+
+		int colonPos = refText.indexOf(':');
+		if (colonPos < 0) {
+			refStrPart1 = refText.trim();
+			refStrPart2 = null;
+		} else {
+			refStrPart1 = refText.substring(0, colonPos).trim();
+			refStrPart2 = refText.substring(colonPos + 1).trim();
+		}
+		return ec.getDynamicReference(workbookName, sheetName, refStrPart1, refStrPart2, isA1style);
+	}
+
+	/**
+	 * @return array of length 2: {workbookName, sheetName,}.  Second element will always be
+	 * present.  First element may be null if sheetName is unqualified.
+	 * Returns <code>null</code> if text cannot be parsed.
+	 */
+	private static String[] parseWorkbookAndSheetName(CharSequence text) {
+		int lastIx = text.length() - 1;
+		if (lastIx < 0) {
+			return null;
+		}
+		if (canTrim(text)) {
+			return null;
+		}
+		char firstChar = text.charAt(0);
+		if (Character.isWhitespace(firstChar)) {
+			return null;
+		}
+		if (firstChar == '\'') {
+			// workbookName or sheetName needs quoting
+			// quotes go around both
+			if (text.charAt(lastIx) != '\'') {
+				return null;
+			}
+			firstChar = text.charAt(1);
+			if (Character.isWhitespace(firstChar)) {
+				return null;
+			}
+			String wbName;
+			int sheetStartPos;
+			if (firstChar == '[') {
+				int rbPos = text.toString().lastIndexOf(']');
+				if (rbPos < 0) {
+					return null;
+				}
+				wbName = unescapeString(text.subSequence(2, rbPos));
+				if (wbName == null || canTrim(wbName)) {
+					return null;
+				}
+				sheetStartPos = rbPos + 1;
+			} else {
+				wbName = null;
+				sheetStartPos = 1;
+			}
+
+			// else - just sheet name
+			String sheetName = unescapeString(text.subSequence(sheetStartPos, lastIx));
+			if (sheetName == null) { // note - when quoted, sheetName can
+									 // start/end with whitespace
+				return null;
+			}
+			return new String[] { wbName, sheetName, };
+		}
+
+		if (firstChar == '[') {
+			int rbPos = text.toString().lastIndexOf(']');
+			if (rbPos < 0) {
+				return null;
+			}
+			CharSequence wbName = text.subSequence(1, rbPos);
+			if (canTrim(wbName)) {
+				return null;
+			}
+			CharSequence sheetName = text.subSequence(rbPos + 1, text.length());
+			if (canTrim(sheetName)) {
+				return null;
+			}
+			return new String[] { wbName.toString(), sheetName.toString(), };
+		}
+		// else - just sheet name
+		return new String[] { null, text.toString(), };
+	}
+
+	/**
+	 * @return <code>null</code> if there is a syntax error in any escape sequence
+	 * (the typical syntax error is a single quote character not followed by another).
+	 */
+	private static String unescapeString(CharSequence text) {
+		int len = text.length();
+		StringBuilder sb = new StringBuilder(len);
+		int i = 0;
+		while (i < len) {
+			char ch = text.charAt(i);
+			if (ch == '\'') {
+				// every quote must be followed by another
+				i++;
+				if (i >= len) {
+					return null;
+				}
+				ch = text.charAt(i);
+				if (ch != '\'') {
+					return null;
+				}
+			}
+			sb.append(ch);
+			i++;
+		}
+		return sb.toString();
+	}
+
+	private static boolean canTrim(CharSequence text) {
+		int lastIx = text.length() - 1;
+		if (lastIx < 0) {
+			return false;
+		}
+		if (Character.isWhitespace(text.charAt(0))) {
+			return true;
+		}
+		if (Character.isWhitespace(text.charAt(lastIx))) {
+			return true;
+		}
+		return false;
 	}
 }
diff --git a/src/java/org/apache/poi/ss/formula/CollaboratingWorkbooksEnvironment.java b/src/java/org/apache/poi/ss/formula/CollaboratingWorkbooksEnvironment.java
index 9a0cd8511f..21d3fd0c6d 100644
--- a/src/java/org/apache/poi/ss/formula/CollaboratingWorkbooksEnvironment.java
+++ b/src/java/org/apache/poi/ss/formula/CollaboratingWorkbooksEnvironment.java
@@ -34,9 +34,15 @@ import java.util.Set;
  * @author Josh Micich
  */
 public final class CollaboratingWorkbooksEnvironment {
-	
+
+	public static final class WorkbookNotFoundException extends Exception {
+		WorkbookNotFoundException(String msg) {
+			super(msg);
+		}
+	}
+
 	public static final CollaboratingWorkbooksEnvironment EMPTY = new CollaboratingWorkbooksEnvironment();
-	
+
 	private final Map<String, WorkbookEvaluator> _evaluatorsByName;
 	private final WorkbookEvaluator[] _evaluators;
 
@@ -48,7 +54,7 @@ public final class CollaboratingWorkbooksEnvironment {
 	public static void setup(String[] workbookNames, WorkbookEvaluator[] evaluators) {
 		int nItems = workbookNames.length;
 		if (evaluators.length != nItems) {
-			throw new IllegalArgumentException("Number of workbook names is " + nItems 
+			throw new IllegalArgumentException("Number of workbook names is " + nItems
 					+ " but number of evaluators is " + evaluators.length);
 		}
 		if (nItems < 1) {
@@ -82,7 +88,7 @@ public final class CollaboratingWorkbooksEnvironment {
 	}
 
 	private static void hookNewEnvironment(WorkbookEvaluator[] evaluators, CollaboratingWorkbooksEnvironment env) {
-		
+
 		// All evaluators will need to share the same cache.
 		// but the cache takes an optional evaluation listener.
 		int nItems = evaluators.length;
@@ -95,12 +101,15 @@ public final class CollaboratingWorkbooksEnvironment {
 			}
 		}
 		EvaluationCache cache = new EvaluationCache(evalListener);
-		
+
 		for(int i=0; i<nItems; i++) {
 			evaluators[i].attachToEnvironment(env, cache, i);
 		}
-		
 	}
+
+	/**
+	 * Completely dismantles all workbook environments that the supplied evaluators are part of
+	 */
 	private void unhookOldEnvironments(WorkbookEvaluator[] evaluators) {
 		Set<CollaboratingWorkbooksEnvironment> oldEnvs = new HashSet<CollaboratingWorkbooksEnvironment>();
 		for(int i=0; i<evaluators.length; i++) {
@@ -114,10 +123,11 @@ public final class CollaboratingWorkbooksEnvironment {
 	}
 
 	/**
-	 * 
+	 * Tell all contained evaluators that this environment should be discarded
 	 */
 	private void unhook() {
 		if (_evaluators.length < 1) {
+			// Never dismantle the EMPTY environment
 			return;
 		}
 		for (int i = 0; i < _evaluators.length; i++) {
@@ -126,7 +136,7 @@ public final class CollaboratingWorkbooksEnvironment {
 		_unhooked = true;
 	}
 
-	public WorkbookEvaluator getWorkbookEvaluator(String workbookName) {
+	public WorkbookEvaluator getWorkbookEvaluator(String workbookName) throws WorkbookNotFoundException {
 		if (_unhooked) {
 			throw new IllegalStateException("This environment has been unhooked");
 		}
@@ -148,7 +158,7 @@ public final class CollaboratingWorkbooksEnvironment {
 				}
 				sb.append(")");
 			}
-			throw new RuntimeException(sb.toString());
+			throw new WorkbookNotFoundException(sb.toString());
 		}
 		return result;
 	}
diff --git a/src/java/org/apache/poi/ss/formula/EvaluationWorkbook.java b/src/java/org/apache/poi/ss/formula/EvaluationWorkbook.java
index 28f31eb497..a3b2325719 100644
--- a/src/java/org/apache/poi/ss/formula/EvaluationWorkbook.java
+++ b/src/java/org/apache/poi/ss/formula/EvaluationWorkbook.java
@@ -34,6 +34,10 @@ public interface EvaluationWorkbook {
 	 * @return -1 if the specified sheet is from a different book
 	 */
 	int getSheetIndex(EvaluationSheet sheet);
+	/**
+	 * Finds a sheet index by case insensitive name.
+	 * @return the index of the sheet matching the specified name.  -1 if not found
+	 */
 	int getSheetIndex(String sheetName);
 
 	EvaluationSheet getSheet(int sheetIndex);
diff --git a/src/java/org/apache/poi/ss/formula/LazyRefEval.java b/src/java/org/apache/poi/ss/formula/LazyRefEval.java
index 69f0834f50..2a97c67c48 100644
--- a/src/java/org/apache/poi/ss/formula/LazyRefEval.java
+++ b/src/java/org/apache/poi/ss/formula/LazyRefEval.java
@@ -34,13 +34,18 @@ final class LazyRefEval extends RefEvalBase {
 
 	private final SheetRefEvaluator _evaluator;
 
-	public LazyRefEval(RefPtg ptg, SheetRefEvaluator sre) {
-		super(ptg.getRow(), ptg.getColumn());
+	public LazyRefEval(int rowIndex, int columnIndex, SheetRefEvaluator sre) {
+		super(rowIndex, columnIndex);
+		if (sre == null) {
+			throw new IllegalArgumentException("sre must not be null");
+		}
 		_evaluator = sre;
 	}
+	public LazyRefEval(RefPtg ptg, SheetRefEvaluator sre) {
+		this(ptg.getRow(), ptg.getColumn(), sre);
+	}
 	public LazyRefEval(Ref3DPtg ptg, SheetRefEvaluator sre) {
-		super(ptg.getRow(), ptg.getColumn());
-		_evaluator = sre;
+		this(ptg.getRow(), ptg.getColumn(), sre);
 	}
 
 	public ValueEval getInnerValueEval() {
diff --git a/src/java/org/apache/poi/ss/formula/OperationEvaluationContext.java b/src/java/org/apache/poi/ss/formula/OperationEvaluationContext.java
new file mode 100644
index 0000000000..2ec5f5791c
--- /dev/null
+++ b/src/java/org/apache/poi/ss/formula/OperationEvaluationContext.java
@@ -0,0 +1,259 @@
+/* ====================================================================
+   Licensed to the Apache Software Foundation (ASF) under one or more
+   contributor license agreements.  See the NOTICE file distributed with
+   this work for additional information regarding copyright ownership.
+   The ASF licenses this file to You under the Apache License, Version 2.0
+   (the "License"); you may not use this file except in compliance with
+   the License.  You may obtain a copy of the License at
+
+       http://www.apache.org/licenses/LICENSE-2.0
+
+   Unless required by applicable law or agreed to in writing, software
+   distributed under the License is distributed on an "AS IS" BASIS,
+   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+   See the License for the specific language governing permissions and
+   limitations under the License.
+==================================================================== */
+
+package org.apache.poi.ss.formula;
+
+import org.apache.poi.hssf.record.formula.AreaI;
+import org.apache.poi.hssf.record.formula.eval.AreaEval;
+import org.apache.poi.hssf.record.formula.eval.ErrorEval;
+import org.apache.poi.hssf.record.formula.eval.RefEval;
+import org.apache.poi.hssf.record.formula.eval.ValueEval;
+import org.apache.poi.ss.SpreadsheetVersion;
+import org.apache.poi.ss.formula.CollaboratingWorkbooksEnvironment.WorkbookNotFoundException;
+import org.apache.poi.ss.formula.EvaluationWorkbook.ExternalSheet;
+import org.apache.poi.ss.util.CellReference;
+import org.apache.poi.ss.util.CellReference.NameType;
+
+/**
+ * Contains all the contextual information required to evaluate an operation
+ * within a formula
+ *
+ * For POI internal use only
+ *
+ * @author Josh Micich
+ */
+public final class OperationEvaluationContext {
+
+	private final EvaluationWorkbook _workbook;
+	private final int _sheetIndex;
+	private final int _rowIndex;
+	private final int _columnIndex;
+	private final EvaluationTracker _tracker;
+	private final WorkbookEvaluator _bookEvaluator;
+
+	public OperationEvaluationContext(WorkbookEvaluator bookEvaluator, EvaluationWorkbook workbook, int sheetIndex, int srcRowNum,
+			int srcColNum, EvaluationTracker tracker) {
+		_bookEvaluator = bookEvaluator;
+		_workbook = workbook;
+		_sheetIndex = sheetIndex;
+		_rowIndex = srcRowNum;
+		_columnIndex = srcColNum;
+		_tracker = tracker;
+	}
+
+	public EvaluationWorkbook getWorkbook() {
+		return _workbook;
+	}
+
+	public int getRowIndex() {
+		return _rowIndex;
+	}
+
+	public int getColumnIndex() {
+		return _columnIndex;
+	}
+
+	/* package */ SheetRefEvaluator createExternSheetRefEvaluator(ExternSheetReferenceToken ptg) {
+		int externSheetIndex = ptg.getExternSheetIndex();
+		ExternalSheet externalSheet = _workbook.getExternalSheet(externSheetIndex);
+		WorkbookEvaluator targetEvaluator;
+		int otherSheetIndex;
+		if (externalSheet == null) {
+			// sheet is in same workbook
+			otherSheetIndex = _workbook.convertFromExternSheetIndex(externSheetIndex);
+			targetEvaluator = _bookEvaluator;
+		} else {
+			// look up sheet by name from external workbook
+			String workbookName = externalSheet.getWorkbookName();
+			try {
+				targetEvaluator = _bookEvaluator.getOtherWorkbookEvaluator(workbookName);
+			} catch (WorkbookNotFoundException e) {
+				throw new RuntimeException(e.getMessage());
+			}
+			otherSheetIndex = targetEvaluator.getSheetIndex(externalSheet.getSheetName());
+			if (otherSheetIndex < 0) {
+				throw new RuntimeException("Invalid sheet name '" + externalSheet.getSheetName()
+						+ "' in bool '" + workbookName + "'.");
+			}
+		}
+		return new SheetRefEvaluator(targetEvaluator, _tracker, otherSheetIndex);
+	}
+
+	/**
+	 * @return <code>null</code> if either workbook or sheet is not found
+	 */
+	private SheetRefEvaluator createExternSheetRefEvaluator(String workbookName, String sheetName) {
+		WorkbookEvaluator targetEvaluator;
+		if (workbookName == null) {
+			targetEvaluator = _bookEvaluator;
+		} else {
+			if (sheetName == null) {
+				throw new IllegalArgumentException("sheetName must not be null if workbookName is provided");
+			}
+			try {
+				targetEvaluator = _bookEvaluator.getOtherWorkbookEvaluator(workbookName);
+			} catch (WorkbookNotFoundException e) {
+				return null;
+			}
+		}
+		int otherSheetIndex = sheetName == null ? _sheetIndex : targetEvaluator.getSheetIndex(sheetName);
+		if (otherSheetIndex < 0) {
+			return null;
+		}
+		return new SheetRefEvaluator(targetEvaluator, _tracker, otherSheetIndex);
+	}
+
+	public SheetRefEvaluator getRefEvaluatorForCurrentSheet() {
+		return new SheetRefEvaluator(_bookEvaluator, _tracker, _sheetIndex);
+	}
+
+
+
+	/**
+	 * Resolves a cell or area reference dynamically.
+	 * @param workbookName the name of the workbook containing the reference.  If <code>null</code>
+	 * the current workbook is assumed.  Note - to evaluate formulas which use multiple workbooks,
+	 * a {@link CollaboratingWorkbooksEnvironment} must be set up.
+	 * @param sheetName the name of the sheet containing the reference.  May be <code>null</code>
+	 * (when <tt>workbookName</tt> is also null) in which case the current workbook and sheet is
+	 * assumed.
+	 * @param refStrPart1 the single cell reference or first part of the area reference.  Must not
+	 * be <code>null</code>.
+	 * @param refStrPart2 the second part of the area reference. For single cell references this
+	 * parameter must be <code>null</code>
+	 * @param isA1Style specifies the format for <tt>refStrPart1</tt> and <tt>refStrPart2</tt>.
+	 * Pass <code>true</code> for 'A1' style and <code>false</code> for 'R1C1' style.
+	 * TODO - currently POI only supports 'A1' reference style
+	 * @return a {@link RefEval} or {@link AreaEval}
+	 */
+	public ValueEval getDynamicReference(String workbookName, String sheetName, String refStrPart1,
+			String refStrPart2, boolean isA1Style) {
+		if (!isA1Style) {
+			throw new RuntimeException("R1C1 style not supported yet");
+		}
+		SheetRefEvaluator sre = createExternSheetRefEvaluator(workbookName, sheetName);
+		if (sre == null) {
+			return ErrorEval.REF_INVALID;
+		}
+		// ugly typecast - TODO - make spreadsheet version more easily accessible
+		SpreadsheetVersion ssVersion = ((FormulaParsingWorkbook)_workbook).getSpreadsheetVersion();
+
+		NameType part1refType = classifyCellReference(refStrPart1, ssVersion);
+		switch (part1refType) {
+			case BAD_CELL_OR_NAMED_RANGE:
+				return ErrorEval.REF_INVALID;
+			case NAMED_RANGE:
+				throw new RuntimeException("Cannot evaluate '" + refStrPart1
+						+ "'. Indirect evaluation of defined names not supported yet");
+		}
+		if (refStrPart2 == null) {
+			// no ':'
+			switch (part1refType) {
+				case COLUMN:
+				case ROW:
+					return ErrorEval.REF_INVALID;
+				case CELL:
+					CellReference cr = new CellReference(refStrPart1);
+					return new LazyRefEval(cr.getRow(), cr.getCol(), sre);
+			}
+			throw new IllegalStateException("Unexpected reference classification of '" + refStrPart1 + "'.");
+		}
+		NameType part2refType = classifyCellReference(refStrPart1, ssVersion);
+		switch (part2refType) {
+			case BAD_CELL_OR_NAMED_RANGE:
+				return ErrorEval.REF_INVALID;
+			case NAMED_RANGE:
+				throw new RuntimeException("Cannot evaluate '" + refStrPart1
+						+ "'. Indirect evaluation of defined names not supported yet");
+		}
+
+		if (part2refType != part1refType) {
+			// LHS and RHS of ':' must be compatible
+			return ErrorEval.REF_INVALID;
+		}
+		int firstRow, firstCol, lastRow, lastCol;
+		switch (part1refType) {
+			case COLUMN:
+				firstRow =0;
+				lastRow = ssVersion.getLastRowIndex();
+				firstCol = parseColRef(refStrPart1);
+				lastCol = parseColRef(refStrPart2);
+				break;
+			case ROW:
+				firstCol = 0;
+				lastCol = ssVersion.getLastColumnIndex();
+				firstRow = parseRowRef(refStrPart1);
+				lastRow = parseRowRef(refStrPart2);
+				break;
+			case CELL:
+				CellReference cr;
+				cr = new CellReference(refStrPart1);
+				firstRow = cr.getRow();
+				firstCol = cr.getCol();
+				cr = new CellReference(refStrPart2);
+				lastRow = cr.getRow();
+				lastCol = cr.getCol();
+				break;
+			default:
+				throw new IllegalStateException("Unexpected reference classification of '" + refStrPart1 + "'.");
+		}
+		return new LazyAreaEval(new AI(firstRow, firstCol, lastRow, lastCol), sre);
+	}
+
+	private static int parseRowRef(String refStrPart) {
+		return CellReference.convertColStringToIndex(refStrPart);
+	}
+
+	private static int parseColRef(String refStrPart) {
+		return Integer.parseInt(refStrPart) - 1;
+	}
+
+	private static final class AI implements AreaI {
+
+		private final int _fr;
+		private final int _lr;
+		private final int _fc;
+		private final int _lc;
+
+		public AI(int fr, int fc, int lr, int lc) {
+			_fr = Math.min(fr, lr);
+			_lr = Math.max(fr, lr);
+			_fc = Math.min(fc, lc);
+			_lc = Math.max(fc, lc);
+		}
+		public int getFirstColumn() {
+			return _fc;
+		}
+		public int getFirstRow() {
+			return _fr;
+		}
+		public int getLastColumn() {
+			return _lc;
+		}
+		public int getLastRow() {
+			return _lr;
+		}
+	}
+
+	private static NameType classifyCellReference(String str, SpreadsheetVersion ssVersion) {
+		int len = str.length();
+		if (len < 1) {
+			return CellReference.NameType.BAD_CELL_OR_NAMED_RANGE;
+		}
+		return CellReference.classifyCellReference(str, ssVersion);
+	}
+}
diff --git a/src/java/org/apache/poi/ss/formula/OperationEvaluatorFactory.java b/src/java/org/apache/poi/ss/formula/OperationEvaluatorFactory.java
index b459b4c99c..449c0b18c6 100755
--- a/src/java/org/apache/poi/ss/formula/OperationEvaluatorFactory.java
+++ b/src/java/org/apache/poi/ss/formula/OperationEvaluatorFactory.java
@@ -106,8 +106,8 @@ final class OperationEvaluatorFactory {
 			_numberOfOperands = argCount;
 		}
 
-		public ValueEval evaluate(ValueEval[] args, int rowIndex, short columnIndex) {
-			return _function.evaluate(args, rowIndex, columnIndex);
+		public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) {
+			return _function.evaluate(args, ec.getRowIndex(), (short) ec.getColumnIndex());
 		}
 
 		public int getNumberOfOperands() {
diff --git a/src/java/org/apache/poi/ss/formula/SheetRefEvaluator.java b/src/java/org/apache/poi/ss/formula/SheetRefEvaluator.java
index b2a6ed123d..97568cfec4 100644
--- a/src/java/org/apache/poi/ss/formula/SheetRefEvaluator.java
+++ b/src/java/org/apache/poi/ss/formula/SheetRefEvaluator.java
@@ -27,14 +27,15 @@ final class SheetRefEvaluator {
 
 	private final WorkbookEvaluator _bookEvaluator;
 	private final EvaluationTracker _tracker;
-	private final EvaluationSheet _sheet;
 	private final int _sheetIndex;
+	private EvaluationSheet _sheet;
 
-	public SheetRefEvaluator(WorkbookEvaluator bookEvaluator, EvaluationTracker tracker,
-			EvaluationWorkbook _workbook, int sheetIndex) {
+	public SheetRefEvaluator(WorkbookEvaluator bookEvaluator, EvaluationTracker tracker, int sheetIndex) {
+		if (sheetIndex < 0) {
+			throw new IllegalArgumentException("Invalid sheetIndex: " + sheetIndex + ".");
+		}
 		_bookEvaluator = bookEvaluator;
 		_tracker = tracker;
-		_sheet = _workbook.getSheet(sheetIndex);
 		_sheetIndex = sheetIndex;
 	}
 
@@ -43,6 +44,13 @@ final class SheetRefEvaluator {
 	}
 
 	public ValueEval getEvalForCell(int rowIndex, int columnIndex) {
-		return _bookEvaluator.evaluateReference(_sheet, _sheetIndex, rowIndex, columnIndex, _tracker);
+		return _bookEvaluator.evaluateReference(getSheet(), _sheetIndex, rowIndex, columnIndex, _tracker);
+	}
+
+	private EvaluationSheet getSheet() {
+		if (_sheet == null) {
+			_sheet = _bookEvaluator.getSheet(_sheetIndex);
+		}
+		return _sheet;
 	}
 }
diff --git a/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java b/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java
index abb925713e..806c002bb1 100644
--- a/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java
+++ b/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java
@@ -51,7 +51,6 @@ import org.apache.poi.hssf.record.formula.eval.AreaEval;
 import org.apache.poi.hssf.record.formula.eval.BlankEval;
 import org.apache.poi.hssf.record.formula.eval.BoolEval;
 import org.apache.poi.hssf.record.formula.eval.ErrorEval;
-import org.apache.poi.hssf.record.formula.eval.FunctionEval;
 import org.apache.poi.hssf.record.formula.eval.MissingArgEval;
 import org.apache.poi.hssf.record.formula.eval.NameEval;
 import org.apache.poi.hssf.record.formula.eval.NameXEval;
@@ -61,7 +60,7 @@ import org.apache.poi.hssf.record.formula.eval.RefEval;
 import org.apache.poi.hssf.record.formula.eval.StringEval;
 import org.apache.poi.hssf.record.formula.eval.ValueEval;
 import org.apache.poi.hssf.util.CellReference;
-import org.apache.poi.ss.formula.EvaluationWorkbook.ExternalSheet;
+import org.apache.poi.ss.formula.CollaboratingWorkbooksEnvironment.WorkbookNotFoundException;
 import org.apache.poi.ss.formula.eval.NotImplementedException;
 import org.apache.poi.ss.usermodel.Cell;
 
@@ -80,10 +79,12 @@ public final class WorkbookEvaluator {
 
 	private final EvaluationWorkbook _workbook;
 	private EvaluationCache _cache;
+	/** part of cache entry key (useful when evaluating multiple workbooks) */
 	private int _workbookIx;
 
 	private final IEvaluationListener _evaluationListener;
 	private final Map<EvaluationSheet, Integer> _sheetIndexesBySheet;
+	private final Map<String, Integer> _sheetIndexesByName;
 	private CollaboratingWorkbooksEnvironment _collaboratingWorkbookEnvironment;
 	private final IStabilityClassifier _stabilityClassifier;
 
@@ -96,6 +97,7 @@ public final class WorkbookEvaluator {
 		_evaluationListener = evaluationListener;
 		_cache = new EvaluationCache(evaluationListener);
 		_sheetIndexesBySheet = new IdentityHashMap<EvaluationSheet, Integer>();
+		_sheetIndexesByName = new IdentityHashMap<String, Integer>();
 		_collaboratingWorkbookEnvironment = CollaboratingWorkbooksEnvironment.EMPTY;
 		_workbookIx = 0;
 		_stabilityClassifier = stabilityClassifier;
@@ -108,6 +110,10 @@ public final class WorkbookEvaluator {
 		return _workbook.getSheetName(sheetIndex);
 	}
 
+	/* package */ EvaluationSheet getSheet(int sheetIndex) {
+		return _workbook.getSheet(sheetIndex);
+	}
+
 	private static boolean isDebugLogEnabled() {
 		return false;
 	}
@@ -125,11 +131,22 @@ public final class WorkbookEvaluator {
 		return _collaboratingWorkbookEnvironment;
 	}
 
+	/**
+	 * Discards the current workbook environment and attaches to the default 'empty' environment.
+	 * Also resets evaluation cache.
+	 */
 	/* package */ void detachFromEnvironment() {
 		_collaboratingWorkbookEnvironment = CollaboratingWorkbooksEnvironment.EMPTY;
 		_cache = new EvaluationCache(_evaluationListener);
 		_workbookIx = 0;
 	}
+	/**
+	 * @return the evaluator for another workbook which is part of the same {@link CollaboratingWorkbooksEnvironment}
+	 */
+	/* package */ WorkbookEvaluator getOtherWorkbookEvaluator(String workbookName) throws WorkbookNotFoundException {
+		return _collaboratingWorkbookEnvironment.getWorkbookEvaluator(workbookName);
+	}
+
 	/* package */ IEvaluationListener getEvaluationListener() {
 		return _evaluationListener;
 	}
@@ -179,6 +196,23 @@ public final class WorkbookEvaluator {
 		return evaluateAny(srcCell, sheetIndex, srcCell.getRowIndex(), srcCell.getColumnIndex(), new EvaluationTracker(_cache));
 	}
 
+	/**
+	 * Case-insensitive.
+	 * @return -1 if sheet with specified name does not exist
+	 */
+	/* package */ int getSheetIndex(String sheetName) {
+		Integer result = _sheetIndexesByName.get(sheetName);
+		if (result == null) {
+			int sheetIndex = _workbook.getSheetIndex(sheetName);
+			if (sheetIndex < 0) {
+				return -1;
+			}
+			result = new Integer(sheetIndex);
+			_sheetIndexesByName.put(sheetName, result);
+		}
+		return result.intValue();
+	}
+
 
 	/**
 	 * @return never <code>null</code>, never {@link BlankEval}
@@ -207,15 +241,16 @@ public final class WorkbookEvaluator {
 			if (!tracker.startEvaluate(cce)) {
 				return ErrorEval.CIRCULAR_REF_ERROR;
 			}
+			OperationEvaluationContext ec = new OperationEvaluationContext(this, _workbook, sheetIndex, rowIndex, columnIndex, tracker);
 
 			try {
 
 				Ptg[] ptgs = _workbook.getFormulaTokens(srcCell);
 				if (evalListener == null) {
-					result = evaluateFormula(sheetIndex, rowIndex, columnIndex, ptgs, tracker);
+					result = evaluateFormula(ec, ptgs);
 				} else {
 					evalListener.onStartEvaluate(srcCell, cce, ptgs);
-					result = evaluateFormula(sheetIndex, rowIndex, columnIndex, ptgs, tracker);
+					result = evaluateFormula(ec, ptgs);
 					evalListener.onEndEvaluate(cce, result);
 				}
 
@@ -286,7 +321,7 @@ public final class WorkbookEvaluator {
 		throw new RuntimeException("Unexpected cell type (" + cellType + ")");
 	}
 	// visibility raised for testing
-	/* package */ ValueEval evaluateFormula(int sheetIndex, int srcRowNum, int srcColNum, Ptg[] ptgs, EvaluationTracker tracker) {
+	/* package */ ValueEval evaluateFormula(OperationEvaluationContext ec, Ptg[] ptgs) {
 
 		Stack<ValueEval> stack = new Stack<ValueEval>();
 		for (int i = 0, iSize = ptgs.length; i < iSize; i++) {
@@ -329,12 +364,12 @@ public final class WorkbookEvaluator {
 					ops[j] = p;
 				}
 //				logDebug("invoke " + operation + " (nAgs=" + numops + ")");
-				opResult = invokeOperation(operation, ops, _workbook, sheetIndex, srcRowNum, srcColNum);
+				opResult = operation.evaluate(ops, ec);
 				if (opResult == MissingArgEval.instance) {
 					opResult = BlankEval.INSTANCE;
 				}
 			} else {
-				opResult = getEvalForPtg(ptg, sheetIndex, tracker);
+				opResult = getEvalForPtg(ptg, ec);
 			}
 			if (opResult == null) {
 				throw new RuntimeException("Evaluation result must not be null");
@@ -347,7 +382,7 @@ public final class WorkbookEvaluator {
 		if (!stack.isEmpty()) {
 			throw new IllegalStateException("evaluation stack not empty");
 		}
-		value = dereferenceValue(value, srcRowNum, srcColNum);
+		value = dereferenceValue(value, ec.getRowIndex(), ec.getColumnIndex());
 		if (value == BlankEval.INSTANCE) {
 			// Note Excel behaviour here. A blank final final value is converted to zero.
 			return NumberEval.ZERO;
@@ -384,31 +419,6 @@ public final class WorkbookEvaluator {
 		return evaluationResult;
 	}
 
-	private static ValueEval invokeOperation(OperationEval operation, ValueEval[] ops,
-			EvaluationWorkbook workbook, int sheetIndex, int srcRowNum, int srcColNum) {
-
-		if(operation instanceof FunctionEval) {
-			FunctionEval fe = (FunctionEval) operation;
-			if(fe.isFreeRefFunction()) {
-				return fe.getFreeRefFunction().evaluate(ops, workbook, sheetIndex, srcRowNum, srcColNum);
-			}
-		}
-		return operation.evaluate(ops, srcRowNum, (short)srcColNum);
-	}
-	private SheetRefEvaluator createExternSheetRefEvaluator(EvaluationTracker tracker,
-			ExternSheetReferenceToken ptg) {
-		int externSheetIndex = ptg.getExternSheetIndex();
-		ExternalSheet externalSheet = _workbook.getExternalSheet(externSheetIndex);
-		if (externalSheet != null) {
-			WorkbookEvaluator otherEvaluator = _collaboratingWorkbookEnvironment.getWorkbookEvaluator(externalSheet.getWorkbookName());
-			EvaluationWorkbook otherBook = otherEvaluator._workbook;
-			int otherSheetIndex = otherBook.getSheetIndex(externalSheet.getSheetName());
-			return new SheetRefEvaluator(otherEvaluator, tracker, otherBook, otherSheetIndex);
-		}
-		int otherSheetIndex = _workbook.convertFromExternSheetIndex(externSheetIndex);
-		return new SheetRefEvaluator(this, tracker, _workbook, otherSheetIndex);
-
-	}
 
 	/**
 	 * returns an appropriate Eval impl instance for the Ptg. The Ptg must be
@@ -416,7 +426,7 @@ public final class WorkbookEvaluator {
 	 * StringPtg, BoolPtg <br/>special Note: OperationPtg subtypes cannot be
 	 * passed here!
 	 */
-	private ValueEval getEvalForPtg(Ptg ptg, int sheetIndex, EvaluationTracker tracker) {
+	private ValueEval getEvalForPtg(Ptg ptg, OperationEvaluationContext ec) {
 		//  consider converting all these (ptg instanceof XxxPtg) expressions to (ptg.getClass() == XxxPtg.class)
 
 		if (ptg instanceof NamePtg) {
@@ -427,7 +437,7 @@ public final class WorkbookEvaluator {
 				return new NameEval(nameRecord.getNameText());
 			}
 			if (nameRecord.hasFormula()) {
-				return evaluateNameFormula(nameRecord.getNameDefinition(), sheetIndex, tracker);
+				return evaluateNameFormula(nameRecord.getNameDefinition(), ec);
 			}
 
 			throw new RuntimeException("Don't now how to evalate name '" + nameRecord.getNameText() + "'");
@@ -460,15 +470,15 @@ public final class WorkbookEvaluator {
 		}
 		if (ptg instanceof Ref3DPtg) {
 			Ref3DPtg refPtg = (Ref3DPtg) ptg;
-			SheetRefEvaluator sre = createExternSheetRefEvaluator(tracker, refPtg);
+			SheetRefEvaluator sre = ec.createExternSheetRefEvaluator(refPtg);
 			return new LazyRefEval(refPtg, sre);
 		}
 		if (ptg instanceof Area3DPtg) {
 			Area3DPtg aptg = (Area3DPtg) ptg;
-			SheetRefEvaluator sre = createExternSheetRefEvaluator(tracker, aptg);
+			SheetRefEvaluator sre = ec.createExternSheetRefEvaluator(aptg);
 			return new LazyAreaEval(aptg, sre);
 		}
-		SheetRefEvaluator sre = new SheetRefEvaluator(this, tracker, _workbook, sheetIndex);
+		SheetRefEvaluator sre = ec.getRefEvaluatorForCurrentSheet();
 		if (ptg instanceof RefPtg) {
 			return new LazyRefEval(((RefPtg) ptg), sre);
 		}
@@ -490,11 +500,11 @@ public final class WorkbookEvaluator {
 
 		throw new RuntimeException("Unexpected ptg class (" + ptg.getClass().getName() + ")");
 	}
-	private ValueEval evaluateNameFormula(Ptg[] ptgs, int sheetIndex, EvaluationTracker tracker) {
+	private ValueEval evaluateNameFormula(Ptg[] ptgs, OperationEvaluationContext ec) {
 		if (ptgs.length > 1) {
 			throw new RuntimeException("Complex name formulas not supported yet");
 		}
-		return getEvalForPtg(ptgs[0], sheetIndex, tracker);
+		return getEvalForPtg(ptgs[0], ec);
 	}
 
 	/**