So, I'm trying to get formula for row reference e.g. (=2:2 / =Sheet1!2:2 etc.) that is referenced via named range e.g. test_row
If I use getAddress method for cells it works correctly, but if I define named range e.g. test_row with adress =Sheet1!2:2 and try to acess it's formula via namedRange.getRefersToFormula() I get Sheet1!1048576:1048576. On the other hand, same case for column reference =A:A with named range test_col works properly.
I haven't looked into source code of POI lib, so I can't say whether it's prolem with lib or the way Excel safes formulas for row references.
The question is whether somebody faced that problem and found some workaround?
UPD: thanks to @AlexRichter, I found out that everything described above works for absolute addresses e.g. =$2:$2 or =Sheet1!$2:$2
The question remains why it doesn't work for relative addresses?
Tests were conducted with:
poi: 5.2.5
poi-ooxml: 5.2.5
poi-ooxml-schemas: 4.1.2
openjdk 21.0.2
Named ranges usually are set to refer to absolute relations. That is =Sheet1!$2:$2 instead of the relative =Sheet1!2:2 for example. Named ranges which refer to relative relations have strange behaviour.
If one sets a named range to refers to =Sheet1!2:2 while a cell in row 3 is the active cell, then this named range refers to =Sheet1!2:2 only if a cell in row 3 is the active cell. If a cell in row 2 is the active cell, then this named range refers to =Sheet1!1:1. And if a cell in row 1 is the active cell, then this named range refers to =Sheet1!1048576:1048576. Excel stores what the named range refers to when cell A1 is the active cell. So it stores =Sheet1!1048576:1048576. And this is what you get.
The absolute reference =Sheet1!$2:$2 is independent of the active cell. Thus a named range which refers to =Sheet1!$2:$2 will always refer to that reference.