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 I'm not sure what you are trying to achieve.
But IMHO, the basic principle should be that any cell a lambda needs, must be passed as one of its arguments. So the solution is simple enough: just add an argument to the lambda so you can pass the cell.
To satisfy your curiosity, I was programming a SUM replacement where MYSUM would exclude itself from the summation, so that I could present a total of a range without consideration to where that total is presented.
ecovonrein This appears to work:
=LAMBDA(in,REDUCE(0,in,LAMBDA(a,b,IF(CELL("address",b)=ADDRESS(ROW(),COLUMN()),a,a+b))))- It might. Feels like overkill. I think the short answer to my original question is "No, there is no simple way."
BTW, I am no longer too sad about the (lack of an) answer because my coding has meanwhile moved on and I am back to working around the issue with a much cleaner SUMIFS. 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.