Cyclic Reference in Formula Error

Topics: Developer Forum, User Forum
May 16, 2013 at 12:44 AM
I am trying to read a Excel file with formula's in most of my columns. Then I receive this error.

PHP Fatal error: Uncaught exception 'PHPExcel_Exception' with message 'ISM-XE1203FI63TRX91!AI7 -> ISM-XE1203FI63TRX91!AO9 -> ISM-XE1203FI63TRX91!AN9 -> ISM-XE1203FI63TRX91!AM9 -> ISM-XE1203FI63TRX91!AP9 -> ISM-XE1203FI63TRX91!AN9 -> Cyclic Reference in Formula' in /abc//Classes/PHPExcel/Cell.php:307\nStack trace:\n#0 /abc/reader.php(226): PHPExcel_Cell->getCalculatedValue()\n#1 /abc//reader.php(132): calculateFormula(Object(PHPExcel_Worksheet), 'AI7')\n#2 /abc/reader.php(36): readTemplateData('tmp/Template...')\n#3 {main}\n thrown in /abc/Classes/PHPExcel/Cell.php on line 307

Any idea how to resolve this ?.
Coordinator
May 16, 2013 at 6:46 AM
If your formulae contain cyclic references, then you need to choose how to deal with them, as you do in MS Excel itself. The default behaviour is to throw an Exception like this, which is similar to the way MS Excel will behave by default.
The alternative is to tell PHPExcel to follow the cycle for a fixed number of iterations by setting the calculation engine to:
PHPExcel_Calculation::getInstance()->cyclicFormulaCount = 1;
or to any value > =1 indicating the number of iterations to cycle
Aug 8, 2013 at 4:04 PM
Fatal error: Uncaught exception 'PHPExcel_Calculation_Exception' with message 'Summary!B12 -> FinancialDataValues!M8 -> FinancialData!V7 -> ForecastsData!C7 -> FinancialDataValues!M8 -> Cyclic Reference in Formula' in E:\xampp-portable\htdocs\ValueCalculator\libs\php\PHPExcel\Cell.php:298 Stack trace: #0 E:\xampp-portable\htdocs\ValueCalculator\libs\php\PHPExcel\Writer\Excel2007\Worksheet.php(1078): PHPExcel_Cell->getCalculatedValue() #1 E:\xampp-portable\htdocs\ValueCalculator\libs\php\PHPExcel\Writer\Excel2007\Worksheet.php(1025): PHPExcel_Writer_Excel2007_Worksheet->_writeCell(Object(PHPExcel_Shared_XMLWriter), Object(PHPExcel_Worksheet), 'B12', Array, Array) #2 E:\xampp-portable\htdocs\ValueCalculator\libs\php\PHPExcel\Writer\Excel2007\Worksheet.php(83): PHPExcel_Writer_Excel2007_Worksheet->_writeSheetData(Object(PHPExcel_Shared_XMLWriter), Object(PHPExcel_Worksheet), Array) #3 E:\xampp-portable\htdocs\ValueCalculator\libs\php\PHPExcel\Writer\Excel2007.php(262): PHPExcel_Writer_Excel2007_Worksheet->writeWorksheet(Obje in E:\xampp-portable\htdocs\ValueCalculator\libs\php\PHPExcel\Cell.php on line 298

the problem is that my summary!b12: =INDEX(FinancialDataValues!A8:AS8,B10+Variables!A5)
where B10 is -2001
Variable!A5 is 2010
So that should return me the index from financialdatavalues from column 9 if i'm not wrong....when it try to calculate each value until it gets to position 9.

financialdatavalues!A8:I8 {'', "Sales", =FinancialData!C7, =FinancialData!E7 ,=FinancialData!G7, =FinancialData!I7, =FinancialData!K7, =FinancialData!M7, =FinancialData!O7}
where my financialdata!o7 is empty... why it return me cyclic reference?

there isn't any way to bypass it?

thank you!
Coordinator
Aug 8, 2013 at 4:18 PM
The message is pretty self explanatory:
  • Cell Summary!B12 references cell FinancialDataValues!M8
  • Cell FinancialDataValues!M8 references cell FinancialData!V7
  • Cell FinancialData!V7 references cell ForecastsData!C7
  • Cell ForecastsData!C7 references cell FinancialDataValues!M8
Does using
PHPExcel_Calculation::getInstance()->cyclicFormulaCount = 1;
work? or haven't you tried it?
Aug 8, 2013 at 5:27 PM
Edited Aug 8, 2013 at 5:28 PM
Hello Mark,
I had even tried to put :
PHPExcel_Calculation::getInstance()->cyclicFormulaCount = 400;

without any luck... isn't working...

and isn't cyclic...tried it in excel and isn't cyclic at all...phpexcel interprets it like being cycle....

cause in the index array i have some formulas.... in those formulas another formulas... most of them finishing with a IF or with a empty space result... and instead going to my position from index it calculates each one ...

btw: I have a fix for your index cause isn't working like it should(in excel) ->
                       if($columnNum==0 && $rowNum>0 && count($rowKeys)==1)
            {
                $columnNum=$rowNum;
                $rowNum=$rowKeys[0];
                $columnKey=$columnKeys[$columnNum-1];
                $columnKey=key($arrayValues[$rowNum]);
                return $arrayValues[$rowNum][$columnKey];
            }
at line 622 in LookupRef.php from the Calculation folder...

the column case for index was ok, the column and row was ok, except the row one which can be fixed with this...

regarding my main problem... I'm open to suggestion :)
Coordinator
Aug 8, 2013 at 7:50 PM
PHPExcel !== MS Excel

When I started writing the current PHPExcel calculation engine, it was a learning experience, so I took a relatively simplistic approach using a tried and tested (and easy to understand) approach. It was a significant improvement on the previous version (which used PHP's eval function) severely limiting its functionality (^ was treated as a bitwise operation rather than a power, or a space simply as whitespace), prone to a good many errors, and making it almost impossible to debug.

The PHPExcel calculation engine uses a LALR Parser written in PHP to lex and evaluate formulae. This means that in an expression like =INDEX(FinancialDataValues!A8:AS8,B10+Variables!A5), it evaluates each of the arguments for the function before evaluating the function itself. It means that it evaluates each cell in the range FinancialDataValues!A8:AS8 before it evaluates the INDEX() function which (in this case) gives a cyclic reference as shown in the exception trace. Because there is a cyclic reference within that range, the cyclic exception is thrown when cyclicFormulaCount is <= 0;

MS Excel itself uses a more complex lexer and evaluator that permits it to take a more intelligent approach to evaluating particular expressions and (more specifically) functions which allows it to evaluate the arguments only as needed. As an example
    =IF(A1>10, MOD(A1, 10), A1)
PHPExcel will evaluate the results of A1>10, MOD(A1,10) and A1, then pass all three results to the IF function, which tests A1>10 and returns the appropriate pre-evaluated result. MS Excel's complexity allows it to know to evaluate A1>10 first, and then only evaluate either MOD(A1, 10) or A1 depending on the result of A1>10.

So while PHPExcel tries to emulate the functionality of MS Excel as much as possible, the use of a LALR parser does have limitations. I'm currently working on a major rewrite of the calc engine, but it will still be LALR-based (just cleaner, faster code) unless I can find a predictive algorithm that I can get my head round.


Setting
PHPExcel_Calculation::getInstance()->cyclicFormulaCount =1;
before any formula evaluation should prompt the calc engine to handle a cyclic situation and return a result rather than throw an exception. Using a value > 1 will actively change the cyclic values each iteration, while using a value = 1 won't change any cyclic results, but simply use them in the formula.

However, prior to PHPExcel 1.7.9, the calculation engine was implemented as a singleton, which caused problems for people working with multiple workbooks. In version 1.7.9, I refactored the calculation engine so that there was one global calculation engine (usable when evaluating Excel formulae without instantiating or loading a workbook), and each workbook also had its own calculation engine; so when changing setting (such as the cyclicFormulaCount) you need to identify which instance you want it to apply to.
PHPExcel_Calculation::getInstance(
    $objPHPExcel->getID()
)->cyclicFormulaCount =1;
where $objPHPExcel is your workbook instance

P.S.

I'll look at your fix to INDEX().... despite my protestations that 1.7.9 will be the last of the 1.x branch, I'll probably be releasing a 1.8.0 version.
Aug 10, 2013 at 7:44 AM
Maybe you can still help me with something...

just to stop the parser calculating all my range values for the index function I decided to rewrite it....but got stuck at the parameters... I need their value calculated but not the range itself.... just the 2 and optionally 3rd one for the INDEX function

In Calculation.php -> _calculateFormulaValue
// the bypass for index cyclic formula
        if(strpos($formula,'INDEX(')!==false)
        {
            preg_match_all("/(INDEX\()[^\)]+(?=\))\)/", $formula, $matches);
            for($v=0;$v<sizeof($matches[0]);$v++)
            {
                $curForm=$matches[0][$v];               
                $with='';               
                $nrParam=substr_count($curForm, ',')+1;
                
                preg_match_all("/INDEX\((.+?),/", $curForm, $par1);
                $param1=str_replace("INDEX(","",str_replace(",","",$par1[0][0]));               
                
                if(strpos($param1, "{"))
                    break;
                $worksheet='';$range='';
                
                if(strpos($param1, "!")){
                    $worksheet_=preg_split("/[!]/", $param1);
                    $worksheet=$worksheet_[0];
                    $range=preg_split("/[:]/", $worksheet_[1]);
                }
                else{
                    $range=preg_split("/[:]/", $param1);
                }
                
                $s_row=preg_replace("/[^0-9]/","",$range[0]);
                $f_row=preg_replace("/[^0-9]/","",$range[1]);
                $s_col=preg_replace("/[^a-zA-Z]/","",$range[0]);
                $f_col=preg_replace("/[^a-zA-Z]/","",$range[1]);
                
                $s_collString=PHPExcel_Cell::columnIndexFromString($s_col);
                $f_collString=PHPExcel_Cell::columnIndexFromString($f_col);
                
                $valuesArray=array();
                $c=$r=0;
                for($posC=$s_collString-1;$posC<$f_collString;$posC++)
                {
                    $r=0;
                    $arrayValue='';
                    if($s_row==$f_row)
                    {
                        $valuesArray[$c][0]=PHPExcel_Cell::stringFromColumnIndex($posC).$f_row;
                    }
                    else{                       
                        for($posR=$s_row;$posR<=$f_row;$posR++)
                        {                           
                            $valuesArray[$c][$r]=PHPExcel_Cell::stringFromColumnIndex($posC).$posR;
                            $r++;
                        }
                    }
                    $c++;
                }
                                    
                if($nrParam==2)
                {
                    $param2_=preg_split("/[,]/", $curForm);
                    $param2=rtrim($param2_[1],')');
                    
                    if(!is_numeric($param2))
                    {                       
                        //$param2=$this->_calculateFormulaValue($param2);
//////=> here need to calculate 2nd
                    }
                    
                    echo ($param2-1)."\r\n";
                    if($s_row==$f_row)
                        $with= $valuesArray[$param2][0];
                    else {
                        $with=$valuesArray[0][$param2];
                    }                               
                }
                else if($nrParam==3)
                {
                    $param2_=preg_split("/[,]/", $curForm);                 
                    $param2=$param2_[1]-1;
                    $param3=rtrim($param2_[2],')')-1;
//////=> here need to calculate 2nd and 3rd param
    
                    $with= $valuesArray[$param3][$param2];                                  
                }
                if($worksheet!='')
                {
                    $with=$worksheet.'!'.$with;
                }
                //$formula=preg_replace('/'.$curForm.'/', $with, $formula);
                $formula= str_replace($curForm, $with, $formula);
                //echo $curForm;echo "\r\n";
                //echo $with; echo "\r\n";
                //print_r($valuesArray);
                echo $formula;echo "\r\n";
            }       
        }
can you give me any hint how to get those param calculated? thank you
Aug 14, 2013 at 10:37 AM
solve it

In Calculation.php you need to replace the _calculateFormulaValue function with:
/**
     * Parse a cell formula and calculate its value
     *
     * @param   string          $formula    The formula to parse and calculate
     * @param   string          $cellID     The ID (e.g. A3) of the cell that we are calculating
     * @param   PHPExcel_Cell   $pCell      Cell to calculate
     * @return  mixed
     * @throws  PHPExcel_Calculation_Exception
     */
    public function _calculateFormulaValue($formula, $cellID=null, PHPExcel_Cell $pCell = null) {
        
        $cellValue = '';
        
        //  Basic validation that this is indeed a formula
        //  We simply return the cell value if not
        $formula = trim($formula);
        
        // the bypass for index cyclic formula
        if(strpos($formula,'INDEX(')!==false)
        {
            preg_match_all("/INDEX(\((?:[^()]++|(?1))+\))/", $formula, $matches);
            for($v=0;$v<sizeof($matches[0]);$v++)
            {
                //echo "formula initiala: ".$formula."\r\n";
                $curForm=$matches[0][$v];               
                $with='';               
                $nrParam=substr_count($curForm, ',')+1;
                
                preg_match_all("/INDEX\((.+?),/", $curForm, $par1);
                $param1=str_replace("INDEX(","",str_replace(",","",$par1[0][0]));               
                
                //echo "formula curenta: ".$curForm."\r\n";
                //echo "param 1: ".$param1."\r\n";
                if(strpos($param1, "{"))
                    break;
                $worksheet='';$range='';
                
                            
                if(substr_count($param1, ":")>1)
                {
                    preg_match_all("/([a-zA-Z0-9]+)[!]([A-Z0-9:]+)|([a-zA-Z0-9]+)[:]([A-Z0-9]+)/", $param1, $lotOfRanges);
                    $tempForm='';                   
                    for($v_=0;$v_<sizeof($lotOfRanges[0]);$v_++)
                    {
                        $valuesArray=array();   
                        $parameter=$lotOfRanges[0][$v_];
                        if(strpos($parameter, "!")){
                            $worksheet_=preg_split("/[!]/", $parameter);
                            $worksheet=$worksheet_[0];
                            $range=preg_split("/[:]/", $worksheet_[1]);
                        }
                        else{
                            $range=preg_split("/[:]/", $parameter);
                        }
                        
                        $s_row=preg_replace("/[^0-9]/","",$range[0]);
                        $f_row=preg_replace("/[^0-9]/","",$range[1]);
                        $s_col=preg_replace("/[^a-zA-Z]/","",$range[0]);
                        $f_col=preg_replace("/[^a-zA-Z]/","",$range[1]);
                        
                        $s_collString=PHPExcel_Cell::columnIndexFromString($s_col);
                        $f_collString=PHPExcel_Cell::columnIndexFromString($f_col);
                        
                        
                        $c=$r=0;
                        for($posC=$s_collString-1;$posC<$f_collString;$posC++)
                        {
                            $r=0;
                            $arrayValue='';
                            if($s_row==$f_row)
                            {
                                $valuesArray[$c][0]=PHPExcel_Cell::stringFromColumnIndex($posC).$f_row;
                            }
                            else{                       
                                for($posR=$s_row;$posR<=$f_row;$posR++)
                                {                           
                                    $valuesArray[$c][$r]=PHPExcel_Cell::stringFromColumnIndex($posC).$posR;
                                    $r++;
                                }
                            }
                            $c++;
                        }
                        //print_r($valuesArray);
                        if($nrParam==2)
                        {
                            $param2_=preg_split("/[,]/", $curForm);
                            $param2=rtrim($param2_[1],')');
                            
                            if(!is_numeric($param2))
                            {
                                $par2="=".$param2;
                                $cellValue = $this->_processTokenStack($this->_parseFormula($param2,$pCell), $cellID,$pCell);                       
                                $param2=$cellValue;
                                if(is_array($param2)){
                                    $par2=str_replace("=", "", $par2);
                                    $rr_row=preg_replace("/[^0-9]/","",$par2);
                                    $cc_col=preg_replace("/[^a-zA-Z]/","",$par2);
                                    $param2=$param2[$rr_row][$cc_col];
                                }
                            }
        
                            if($s_row==$f_row)
                                $tempForm= $valuesArray[$param2-1][0];
                            else {
                                $tempForm=$valuesArray[0][$param2-1];
                            }                               
                        }
                        else if($nrParam==3)
                        {
                            $param2_=preg_split("/[,]/", $curForm);                                     
                            $param2=$param2_[1];
                            if(!is_numeric($param2))
                            {
                                $par2="=".$param2;      
                                $cellValue = $this->_processTokenStack($this->_parseFormula($param2,$pCell), $cellID,$pCell);                       
                                $param2=$cellValue-1;
                            }
                            else
                            {
                                $param2=$param2-1;
                            }
                            
                            $param3=rtrim($param2_[2],')'); 
                            if(!is_numeric($param3))
                            {
                                $par2="=".$param3;      
                                $cellValue = $this->_processTokenStack($this->_parseFormula($param3,$pCell), $cellID,$pCell);                       
                                $param3=$cellValue-1;
                            }
                            else
                            {
                                $param3=$param3-1;
                            }                   
                            $tempForm= $valuesArray[$param3][$param2];                                  
                        }
                                    
                        if($worksheet!='')
                        {
                            $tempForm=$worksheet.'!'.$tempForm;
                        }
                        $param1=str_replace($parameter, $tempForm, $param1);
                    }
                    $with=$param1;
                }
                else 
                {
                    $valuesArray=array();       
                    if(strpos($param1, "!")){
                        $worksheet_=preg_split("/[!]/", $param1);
                        $worksheet=$worksheet_[0];
                        $range=preg_split("/[:]/", $worksheet_[1]);
                    }
                    else{
                        $range=preg_split("/[:]/", $param1);
                    }
                    
                    $s_row=preg_replace("/[^0-9]/","",$range[0]);
                    $f_row=preg_replace("/[^0-9]/","",$range[1]);
                    $s_col=preg_replace("/[^a-zA-Z]/","",$range[0]);
                    $f_col=preg_replace("/[^a-zA-Z]/","",$range[1]);
                    
                    $s_collString=PHPExcel_Cell::columnIndexFromString($s_col);
                    $f_collString=PHPExcel_Cell::columnIndexFromString($f_col);                 
                    
                    $c=$r=0;
                    for($posC=$s_collString-1;$posC<$f_collString;$posC++)
                    {
                        $r=0;
                        $arrayValue='';
                        if($s_row==$f_row)
                        {
                            $valuesArray[$c][0]=PHPExcel_Cell::stringFromColumnIndex($posC).$f_row;
                        }
                        else{                       
                            for($posR=$s_row;$posR<=$f_row;$posR++)
                            {                           
                                $valuesArray[$c][$r]=PHPExcel_Cell::stringFromColumnIndex($posC).$posR;
                                $r++;
                            }
                        }
                        $c++;
                    }
                    //print_r($valuesArray);
                    if($nrParam==2)
                    {
                        $param2_=preg_split("/[,]/", $curForm);
                        $param2=rtrim($param2_[1],')');
                        
                        if(!is_numeric($param2))
                        {
                            $par2="=".$param2;
                            $cellValue = $this->_processTokenStack($this->_parseFormula($param2,$pCell), $cellID,$pCell);                       
                            $param2=$cellValue;
                            
                            if(is_array($param2)){
                                $par2=str_replace("=", "", $par2);
                                $rr_row=preg_replace("/[^0-9]/","",$par2);
                                $cc_col=preg_replace("/[^a-zA-Z]/","",$par2);
                                $param2=$param2[$rr_row][$cc_col];
                            }
                        }
    
                        if($s_row==$f_row)
                            $with= $valuesArray[$param2-1][0];
                        else {                          
                            $with=$valuesArray[0][$param2-1];
                        }                               
                    }
                    else if($nrParam==3)
                    {
                        $param2_=preg_split("/[,]/", $curForm);                                     
                        $param2=$param2_[1];
                        if(!is_numeric($param2))
                        {
                            $par2="=".$param2;      
                            $cellValue = $this->_processTokenStack($this->_parseFormula($param2,$pCell), $cellID,$pCell);                       
                            $param2=$cellValue-1;
                        }
                        else
                        {
                            $param2=$param2-1;
                        }
                        
                        $param3=rtrim($param2_[2],')'); 
                        if(!is_numeric($param3))
                        {
                            $par2="=".$param3;      
                            $cellValue = $this->_processTokenStack($this->_parseFormula($param3,$pCell), $cellID,$pCell);                       
                            $param3=$cellValue-1;
                        }
                        else
                        {
                            $param3=$param3-1;
                        }                   
                        $with= $valuesArray[$param3][$param2];                                  
                    }
                                
                    if($worksheet!='')
                    {
                        $with=$worksheet.'!'.$with;
                    }
                }   
                //echo "index: ".$with."\r\n";
                $formula= str_replace($curForm, $with, $formula);
                //echo "formula finalala: ".$formula."\r\n";                
            }       
        }
        
        if ($formula{0} != '=') return self::_wrapResult($formula);
        $formula = ltrim(substr($formula,1));
        if (!isset($formula{0})) return self::_wrapResult($formula);

        $pCellParent = ($pCell !== NULL) ? $pCell->getWorksheet() : NULL;
        $wsTitle = ($pCellParent !== NULL) ? $pCellParent->getTitle() : "\x00Wrk";

        if (($cellID !== NULL) && ($this->getValueFromCache($wsTitle, $cellID, $cellValue))) {
            return $cellValue;
        }

        if (($wsTitle{0} !== "\x00") && ($this->_cyclicReferenceStack->onStack($wsTitle.'!'.$cellID))) {
            if ($this->cyclicFormulaCount <= 0) {
                return $this->_raiseFormulaError('Cyclic Reference in Formula');
            } elseif (($this->_cyclicFormulaCount >= $this->cyclicFormulaCount) &&
                      ($this->_cyclicFormulaCell == $wsTitle.'!'.$cellID)) {
                return $cellValue;
            } elseif ($this->_cyclicFormulaCell == $wsTitle.'!'.$cellID) {
                ++$this->_cyclicFormulaCount;
                if ($this->_cyclicFormulaCount >= $this->cyclicFormulaCount) {
                    return $cellValue;
                }
            } elseif ($this->_cyclicFormulaCell == '') {
                $this->_cyclicFormulaCell = $wsTitle.'!'.$cellID;
                if ($this->_cyclicFormulaCount >= $this->cyclicFormulaCount) {
                    return $cellValue;
                }
            }
        }

        //  Parse the formula onto the token stack and calculate the value
        $this->_cyclicReferenceStack->push($wsTitle.'!'.$cellID);
        
        $cellValue = $this->_processTokenStack($this->_parseFormula($formula, $pCell), $cellID, $pCell);
        //echo "formula: ".$formula." -> cell: ".$pCell." -> cellid: ".$cellID." -> cellval: ".$cellValue."\r\n";
        $this->_cyclicReferenceStack->pop();

        // Save to calculation cache
        if ($cellID !== NULL) {
            $this->saveValueToCache($wsTitle, $cellID, $cellValue);
        }

        //  Return the calculated value
        return $cellValue;
    }   //  function _calculateFormulaValue()
Aug 14, 2013 at 10:42 AM
In LookupRef.php(under the Calculation folder) replace INDEX function with:
/**
     * INDEX
     *
     * Uses an index to choose a value from a reference or array
     *
     * Excel Function:
     *      =INDEX(range_array, row_num, [column_num])
     *
     * @param   range_array     A range of cells or an array constant
     * @param   row_num         The row in array from which to return a value. If row_num is omitted, column_num is required.
     * @param   column_num      The column in array from which to return a value. If column_num is omitted, row_num is required.
     * @return  mixed           the value of a specified cell or array of cells
     */
    public static function INDEX($arrayValues,$rowNum = 0,$columnNum = 0) {
        
        if (($rowNum < 0) || ($columnNum < 0)) {
            return PHPExcel_Calculation_Functions::VALUE();
        }

        if (!is_array($arrayValues)) {
            return PHPExcel_Calculation_Functions::REF();
        }
    
        $rowKeys = array_keys($arrayValues);
        $columnKeys = @array_keys($arrayValues[$rowKeys[0]]);

        if ($columnNum > count($columnKeys)) {
            return PHPExcel_Calculation_Functions::VALUE();
        } elseif ($columnNum == 0) {
            if ($rowNum == 0) {
                return $arrayValues;
            }
            if($columnNum==0 && $rowNum>0 && count($rowKeys)==1)
            {
                $columnNum=$rowNum;
                $rowNum=$rowKeys[0];
                $columnKey=$columnKeys[$columnNum-1];
                $columnKey=key($arrayValues[$rowNum]);
                return $arrayValues[$rowNum][$columnKey];
            }
            
            $rowNum = $rowKeys[--$rowNum];
            
            $returnArray = array();
            foreach($arrayValues as $arrayColumn) {
                
                if (is_array($arrayColumn)) {
                    if (isset($arrayColumn[$rowNum])) {
                        $returnArray[] = $arrayColumn[$rowNum];
                    } else {                        
                        return $arrayValues[$rowNum];
                    }
                } else {
                    return $arrayValues[$rowNum];
                }
            }
            return $returnArray;
        }
        $columnNum = $columnKeys[--$columnNum];
        if ($rowNum > count($rowKeys)) {
            return PHPExcel_Calculation_Functions::VALUE();
        } elseif ($rowNum == 0) {
            return $arrayValues[$columnNum];
        }
        $rowNum = $rowKeys[--$rowNum];

        return $arrayValues[$rowNum][$columnNum];
    }   //  function INDEX()
Those 2 function had a fix for the index cyclic bug(isn't ok to calculate them and after that to get the index when it use a stack to keep all reff addresses)

So if you will have a index who will call another index of different sheet will still be ok...also I took in consideration the case when you will have more than 1 range...

I really hope that will work for you cause it worked for me and took me one week to get it fixed.

Remember that this fix is working for the last phpexcel version from the git(I prefer to not waste my time with 1.7.9 version)