2

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?

ZygD
  • 22,092
  • 39
  • 79
  • 102
SoftTimur
  • 5,630
  • 38
  • 140
  • 292

1 Answers1

3

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

Mayukh Bhattacharya
  • 12,541
  • 5
  • 21
  • 32