Error in "this row" structured reference calculation (cached result from first row)

[MarkBaker]

This is:

- [X] a bug report
- [ ] a feature request
- [ ] **not** a usage question (ask them on https://stackoverflow.com/questions/tagged/phpspreadsheet or https://gitter.im/PHPOffice/PhpSpreadsheet)

What is the expected behavior?

Each row with the same structured reference formula should calculate the correct result for that row.

What is the current behavior?

Each row duplicates the result from the first calculated row.

Add Table to Worksheet

     +------+---------------+-----+-----+-----+-----+-----------------------------------------+
     | A    | B             | C   | D   | E   | F   | G                                       |
+----+------+---------------+-----+-----+-----+-----+-----------------------------------------+
|  1 | Year | Country       | Q1  | Q2  | Q3  | Q4  | Sales                                   |
|  2 | 2010 | Belgium       | 380 | 390 | 420 | 460 | =SUM(Sales_Data[[#This Row],[Q1]:[Q4]]) |
|  3 | 2010 | France        | 510 | 490 | 460 | 590 | =SUM(Sales_Data[[#This Row],[Q1]:[Q4]]) |
|  4 | 2010 | Germany       | 720 | 680 | 640 | 660 | =SUM(Sales_Data[[#This Row],[Q1]:[Q4]]) |
|  5 | 2010 | Italy         | 440 | 410 | 420 | 450 | =SUM(Sales_Data[[#This Row],[Q1]:[Q4]]) |
|  6 | 2010 | Spain         | 510 | 490 | 470 | 420 | =SUM(Sales_Data[[#This Row],[Q1]:[Q4]]) |
|  7 | 2010 | UK            | 690 | 610 | 620 | 600 | =SUM(Sales_Data[[#This Row],[Q1]:[Q4]]) |
|  8 | 2010 | United States | 790 | 730 | 860 | 850 | =SUM(Sales_Data[[#This Row],[Q1]:[Q4]]) |
|  9 | 2011 | Belgium       | 400 | 350 | 450 | 500 | =SUM(Sales_Data[[#This Row],[Q1]:[Q4]]) |
| 10 | 2011 | France        | 620 | 650 | 415 | 570 | =SUM(Sales_Data[[#This Row],[Q1]:[Q4]]) |
| 11 | 2011 | Germany       | 680 | 620 | 710 | 690 | =SUM(Sales_Data[[#This Row],[Q1]:[Q4]]) |
| 12 | 2011 | Italy         | 430 | 370 | 350 | 335 | =SUM(Sales_Data[[#This Row],[Q1]:[Q4]]) |
| 13 | 2011 | Spain         | 460 | 390 | 430 | 415 | =SUM(Sales_Data[[#This Row],[Q1]:[Q4]]) |
| 14 | 2011 | UK            | 720 | 650 | 580 | 510 | =SUM(Sales_Data[[#This Row],[Q1]:[Q4]]) |
| 15 | 2011 | United States | 800 | 700 | 900 | 950 | =SUM(Sales_Data[[#This Row],[Q1]:[Q4]]) |
+----+------+---------------+-----+-----+-----+-----+-----------------------------------------+

Calculate Structured References

     +------+---------------+-----+-----+-----+-----+-------+
     | A    | B             | C   | D   | E   | F   | G     |
+----+------+---------------+-----+-----+-----+-----+-------+
|  1 | Year | Country       | Q1  | Q2  | Q3  | Q4  | Sales |
|  2 | 2010 | Belgium       | 380 | 390 | 420 | 460 | 1650  |
|  3 | 2010 | France        | 510 | 490 | 460 | 590 | 1650  |
|  4 | 2010 | Germany       | 720 | 680 | 640 | 660 | 1650  |
|  5 | 2010 | Italy         | 440 | 410 | 420 | 450 | 1650  |
|  6 | 2010 | Spain         | 510 | 490 | 470 | 420 | 1650  |
|  7 | 2010 | UK            | 690 | 610 | 620 | 600 | 1650  |
|  8 | 2010 | United States | 790 | 730 | 860 | 850 | 1650  |
|  9 | 2011 | Belgium       | 400 | 350 | 450 | 500 | 1650  |
| 10 | 2011 | France        | 620 | 650 | 415 | 570 | 1650  |
| 11 | 2011 | Germany       | 680 | 620 | 710 | 690 | 1650  |
| 12 | 2011 | Italy         | 430 | 370 | 350 | 335 | 1650  |
| 13 | 2011 | Spain         | 460 | 390 | 430 | 415 | 1650  |
| 14 | 2011 | UK            | 720 | 650 | 580 | 510 | 1650  |
| 15 | 2011 | United States | 800 | 700 | 900 | 950 | 1650  |
+----+------+---------------+-----+-----+-----+-----+-------+

Note that the result for the first calculated row (Belgium 2010) is calculated correctly; but all subsequent calculations use display the same result

What are the steps to reproduce?

Please provide a Minimal, Complete, and Verifiable example of code that exhibits the issue without relying on an external Excel file or a web server:

See Samples/Table/03_Column_Formula.php

If this is an issue with reading a specific spreadsheet file, then it may be appropriate to provide a sample file that demonstrates the problem; but please keep it as small as possible, and sanitize any confidential information before uploading.

### What features do you think are causing the issue

- [ ] Reader
- [ ] Writer
- [ ] Styles
- [ ] Data Validations
- [X] Formula Calculations
- [ ] Charts
- [ ] AutoFilter
- [ ] Form Elements

### Does an issue affect all spreadsheet file formats? If not, which formats are affected?

N/A

### Which versions of PhpSpreadsheet and PHP are affected?

[MarkBaker]

I suspect that this may be a calculation cache issue; but need to test further

Confirmed!!! Calculation is checking the cache and finding a match based on the Structured Reference (Worksheet!Sales_Data[[#This Row],[Q1]:[Q4]]), not on the evaluated Structured Reference.

    [0] => Testing cache value for cell Worksheet!G3
    [1] => Evaluating formula for cell Worksheet!G3
    [2] => Formula for cell Worksheet!G3 is SUM(Sales_Data[[#This Row],[Q1]:[Q4]])
    [3] => Worksheet!G3 => Evaluating Structured Reference Sales_Data[[#This Row],[Q1]:[Q4]] as Cell Range C3:F3
    [4] => Worksheet!G3 => Testing cache value for cell Worksheet!Sales_Data[[#This Row],[Q1]:[Q4]]
    [5] => Worksheet!G3 => Retrieving value for cell Worksheet!Sales_Data[[#This Row],[Q1]:[Q4]] from cache
    [6] => Worksheet!G3 => Evaluated Structured Reference Sales_Data[[#This Row],[Q1]:[Q4]] as value { 380, 390, 420, 460 }
    [7] => Worksheet!G3 => Evaluating Function SUM() with 1 argument
    [8] => Worksheet!G3 => Evaluating SUM ( { 380, 390, 420, 460 } )
    [9] => Worksheet!G3 => Evaluation Result for SUM() function call is an integer number with a value of 1650

[oleibman]

Resolved with PR #3505 in 2023. Closing.