Forum Discussion
Address of the cell invoking a Lambda
Does anyone know whether there is an official way to obtain inside a lambda the address of the invoking cell? I write
... LET(this_row,ROW(A1) ...
and it works because the lambda happens to be injected when the activeCell = A1. It ain't great because the reference actually expands to
... LET(this_row,ROW(Sheet!A1) ...
and when I call that lambda in Sheet2!B20, it implicitly expands to
... LET(this_row,ROW(Sheet1!B20) ...
I tried
... LET(this_row,CELL("ROW") ...
instead but it turned out to be a total fiasco. It seems that CELL returns whatever row my cursor is on when the lambda is called. Works great while you are testing with F2<Enter>. Total disaster when pressing F9 from somewhere.
Thanks.
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"))
12 Replies
Hi ecovonrein
I probably failed to understand what you exactly expect with to obtain inside a lambda the address of the invoking cell as the following does what I understand:
Where B2, D5, B7 & D10 have formula: =MyLambda(ADDRESS(ROW(),COLUMN()))
With MyLamba:
=LAMBDA(adr, "LAMBDA Invoked from " & adr)- first off to my colleagues, I get that the poster may want to get that reference. We use ROW() and COLUMN() all the time expecting them to return the reference to the cells they are in (i.e. you don't have to use ROW(A1)). Also using NAMES in the past we have used these tricks to define ranges based on the source of the calling cell.
I'm not sure what the original poster needs as ROW() and COLUMN() appear to work fine. For example i defined:
a = lambda(indirect(address(row()+1, column()+1)))
and it would return the cell to the bottom right of the calling cell regardless of which sheet it is located.Interesting. Perhaps ADDRESS is more cooperative than CELL? I will not use your code because I avoid INDIRECT at all cost. (INDIRECT breaks the calculation tree.) To do your thing, your are much better off simply writing =lambda(!B2) injecting it into A1. Only trouble with that, as I learned at great expense to myself, is that Excel has a bug relating to such global relative reference such that !B2 Excel will occasionally prefix with the wrong sheet (and you end up with random garbage).😞
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))))
