Jan 14 2023 09:07 AM - edited Jan 14 2023 09:22 AM
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.
Jan 16 2023 08:01 AM
@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.
Jan 16 2023 10:18 AM
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)
Jan 16 2023 10:38 AM
Jan 17 2023 06:13 AM - edited Jan 17 2023 06:19 AM
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).:(
Jan 17 2023 06:18 AM
Jan 17 2023 08:28 AM
@ecovonrein This appears to work:
=LAMBDA(in,REDUCE(0,in,LAMBDA(a,b,IF(CELL("address",b)=ADDRESS(ROW(),COLUMN()),a,a+b))))
Jan 17 2023 08:53 AM - edited Jan 17 2023 09:04 AM
Solution@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"))
Jan 17 2023 09:29 AM
Jan 17 2023 09:45 AM
Jan 17 2023 10:01 AM
Jan 17 2023 10:09 AM
Jan 17 2023 10:25 AM
Jan 17 2023 08:53 AM - edited Jan 17 2023 09:04 AM
Solution@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"))