Forum Discussion
Address of the cell invoking a Lambda
- Jan 17, 2023
ecovonrein First off I completely agree with avoiding INDIRECT and only used that formula as an example of how ROW() and COLUMN() seem to work just fine.
As for your sum application it seems odd that you don't want them to pass a ref to 'this' cell because of circular reference issues but then how do you get the range that has yourself in it?
That said I found something I find kinda cool:
=LET(in,F12:H15,IF(ISREF(in INDIRECT(ADDRESS(ROW(),COLUMN()))), "circular","unique"))this formula will look at the range given for 'in' and determine if it includes the cell it is located in and return "circular" if so. This also worked as an independent LAMBDA function. I know it uses INDIRECT() but the concept of checking for a circular reference is kind of cool.
EDIT: alternate to avoid INDIRECT but use of OFFSET is also using a volatile function so really not much better:
circ = lambda(in, if(isref(in offset($A$1,row()-1,column()-1)),"Circular","Unique"))