When you transfer a value in a Hyperlink in a Jedox Web spreadsheet, and use a named range as target, what currently happens is that the source formula of that named range is changed. For example, if your named range pointed to A1 (formula =Sheet1!A1), and you use Hyperlink to transfer the value "abc" to the named range, then the range source formula is changed to ="abc".
It would maybe be better and more aligned with user expectation if instead, the value "abc" would be transferred to the actual source range. That is, in the example, value "abc" is set in the cell A1.
Since a named rangfe can have any formula as source, there should probably be some check if the source actually is referring to a range.
I would appreciate this change as well and would like to extend it a bit: Instead of referring to a fixed named range with =Sheet1!A1 it would be great if the OFFSET function could as well be used in this context. In Excel you can create the following named ranges…
rngSource = OFFSET(Sheet1!A2, 0, 0, 1, Sheet1!A1)
rngTarget = OFFSET(Sheet1!A3, 0, 0, 1, Sheet1!A1)
…and transfer a variable number of cells from source to target.
Our use case for this functionality is, that we have some data entry reports where we calculate proposals for the forecast months on the worksheet and copy them on PALO.DATA formulas via hyperlink actions. As the number of forecast months changes over the year we always have to resize the source and target addresses of the hyperlink actions.
We also must change the hyperlink actions whenever rows or columns are inserted or deleted in the spreadsheet which is labor-intensive and error prone.
If the OFFSET function could be used in hyperlink action, both problems would be solved.