Calc: INDIRECT() function with Column/Row labels

asked 2018-12-27 21:19:20 +0100

Hi there.

My curiosity is mostly based on question by @JohnSUN and question by @ky.

Assuming we have a rectangular table, where columns are years and rows are days of the year (test spreadsheet with examples is attached), we can use Named Ranges or Column/Row Labels to refer specific column or row. Then we can refer table cell via Year!DayOfTheYear syntax, for example =Year2016!Jan05. This method (subjective) is more elegant and easier to read, than using MATCH(), INDEX(), LOOKUP() and so on.

The next interesting thing is to use such a syntax for referencing some cell dynamically, when Year and Day values are not hard coded in the cell/formula, but are the result of user selection or some calculations. In this context, using Column/Row labels seems much more promising, cause, based on Calc settings, they can be assigned automatically and, depending on columns/rows count in the table, they do not mess Named Ranges list.

Meanwhile, while it is possible to use Named Ranges, combined with INDIRECT() function, to successfully refer desired range, the same functionality fails, when passing calculated column/row label strings to INDIRECT().

So the question is, what is the difference between Named Ranges and Column/Row labels, that makes INDIRECT() treat them differently, even if they both refer the same range address and is there workaround possible?

edit retag flag offensive close merge delete