excellambdaexcel-formulaexcel-lambda

INDIRECT and OFFSET in the function body make a name special

I would like to write a user-defined function CHOOSERANGE that returns a range from 4 coordinates. The first version is as follows:

CHOOSERANGE = LAMBDA(row_min, col_min, row_max, col_max,
    INDIRECT(ADDRESS(row_min, col_min)):INDIRECT(ADDRESS(row_max, col_max)) 
),

When I use it in the worksheet, I realize that the icon in front of the function name CHOOSERANGE is different from normal functions.

enter image description here

Additionally, unlike normal functions, there is no intellisense for CHOOSERANGE.

I realize that it is because there is INDIRECT in the function body. Similarly for OFFSET, if there is OFFSET in a function body, that user-defined function has odd behaviour as well.

Does anyone know if it is possible to define a function that chooses a range and doesn't have this odd behavior?

Solution

  • So, it worked for me, since I am in O365 Insiders Beta Channel Version

    CHOOSERANGE

    fx icon shows when the Beta Channel is enabled.

    • Formula is

    CHOOSERANGE = 
LAMBDA(row_min,col_min,row_max,col_max,
INDIRECT(ADDRESS(row_min, col_min)):
INDIRECT(ADDRESS(row_max, col_max)))

    enter image description here

    How to enable the Beta Channel

    Click File --> Click Account --> Click Office Insider --> Enable Beta Channel

    enter image description here