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.
- This is a good response. Alas, I see two problems with it: 1) I obviously do not need that input, since using it would clearly create a circular reference. 2) It is prone to error - particularly given that every Excel user knows not to reference yourself in the cell you are in ...
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.
