Forum Discussion
Address of the cell invoking a Lambda
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"))
ecovonrein This appears to work:
=LAMBDA(in,REDUCE(0,in,LAMBDA(a,b,IF(CELL("address",b)=ADDRESS(ROW(),COLUMN()),a,a+b))))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"))- I am intrigued by your answer. What exactly does
ISREF(in INDIRECT(ADDRESS(ROW();COLUMN())))
do? I did not think the notation ISREF(a b) would work at all, but it does.
However, you have actually accidentally stumbled upon the answer to the original question. It checks out that ROW() delivers a different - and correct - result from CELL("ROW") when deployed in the Lambda context originally shown. Awesome.- the 'trick' here is using the intersection operation that is built into excel which is simply a space. So ISREF is checking the result inside the () is a valid reference and inside is the 'in' and the 'INDIRECT' separated by a space so it performs an intersection operation. If there is any overlap that overlap is the result but if not, then it is not a reference.
- Thanks. Never used the intersection operator. Must try harder ... 🙂
