Bug 55007

Summary: Add function to indirectly reference formulas
Product: POI Reporter: Graham2011 <golfjunobravo>
Component: HSSFAssignee: POI Developers List <dev>
Status: RESOLVED INVALID    
Severity: enhancement    
Priority: P2    
Version: unspecified   
Target Milestone: ---   
Hardware: PC   
OS: All   

Description Graham2011 2013-05-22 16:37:07 UTC
Have a function in OO Calc that allows a cell with a formula to be indirectly evaluated.  This will allow a single formula to be referenced in multiple other cells with any changes in the reference formula being reflected in the other cells.

Sheet1.A1 has formula =SUM(D4:D9;E4:E6)
Sheet2.C9 has a pointer to Sheet1.A1
Sheet3.C9 has a pointer to Sheet1.A1
Sheet4.H3 has a pointer to Sheet1.A1

Desired evaluation result for cell Sheet2.C9 is =SUM(Sheet2.D4:D9;Sheet2.E4:E6)
Desired evaluation result for cell Sheet3.C9 is =SUM(Sheet3.D4:D9;Sheet2.E4:E6)
Desired evaluation result for cell Sheet4.H3 is =SUM(Sheet4.D4:D9;Sheet2.E4:E6)

Optional function feature would be to allow a row and column offset that would offset non-absolute value formula references.
Comment 1 Mark B 2013-05-25 07:21:00 UTC
Slightly confused about what you are asking for here. Are you saying that you want a new function within Excel itself that exhibits this feature? If so, then you really need to post this request to Microsoft as POI is only used to create/edit files that are compatible with the application, it cannot be used to modify the application itself.

Alternativly, you need to create a user defined function - these can be included into the application - but I am not at all sure you could call it SUM as I suspect that is a reserved word or name within the application. To do so, you will be writing the VBA code again within Excel and this is another question best asked on an Excel users forum.

Finally, you might be asking if it is possible to make POI's formula evaluator work in the manner you describe. If so, this might be possible as you can create functions outside of Excel and have POI use these during it's evaluation of the workbook. Again though, I suspect you will fall foul of naming issues if you still want to call it the SUM function.
Comment 2 Javen O'Neal 2016-04-05 17:03:03 UTC
How does Excel handle a workbook saved by OO Calc with "function pointers"?

If it's not in the BIFF or OOXML spreadsheet specs, it would be difficult to implement this in a cross-application compatible way.

Closing due to no progress in several years. If this functionality is still needed, please reopen, provide a workbook with "function pointers" saved by OO Calc, and a patch that implements this behavior in POI in a standards-compatible way.