Forum Discussion

ecovonrein's avatar
ecovonrein
Iron Contributor
Jan 14, 2023
Solved

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 ...
excel
  • mtarler's avatar
    mtarler
    Jan 17, 2023

    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"))
Lorenzo's avatar
Lorenzo
Silver Contributor
Jan 16, 2023

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)

 

mtarler's avatar
mtarler
Silver Contributor
Jan 16, 2023
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.
  • ecovonrein's avatar
    ecovonrein
    Iron Contributor
    Jan 17, 2023

    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).😞

Resources