Address of the cell invoking a Lambda

[ecovonrein]

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.

[#2]

@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.

[#3]

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)

[#4]

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.

[#5]

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).:(

[#6]

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.

[#7]

@ecovonrein This appears to work:

=LAMBDA(in,REDUCE(0,in,LAMBDA(a,b,IF(CELL("address",b)=ADDRESS(ROW(),COLUMN()),a,a+b))))

[#8 · Accepted 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"))

[#9]

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.

[#10]

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.

[#11]

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.

[#12]

Thanks. Never used the intersection operator. Must try harder ... :)

[#13]

to be honest, I think this is the first time I've actually used it (not counting when I first learned about it and 'played' with it).