How can I call "ROW()" and "INDEX()" from within a custom Function?

B.M · June 20, 2021, 10:20pm

I need a function that can multiply two specific cells on whatever row it’s called from. I have it defined as follows, with “AllData” being a named range for my sheet’s cells:

Function GET_MY_DATA()
    my_row = ROW()
    base_amt = INDEX(AllData, my_row, 2)
    extra_scale = INDEX(AllData, my_row, 4)
    GET_MY_DATA = base_amt * extra_scale
End Function

However, when I run this function, I get the following error on the first ‘INDEX()’ call: “BASIC runtime error. Sub-procedure or function procedure not defined.”

karolus · June 21, 2021, 6:33am

tell about the problem, not about you want to solve it. x-y-problem

mikekaganski · June 21, 2021, 6:49am

It is impossible for a user-defined function to obtain information about the cell that is calling it. The function only knows the values of arguments passed to it. Whatever information you need in the function, you must pass as arguments.

Use evaluator’s by-intersection reference, where passing a range as an argument results in a scalar value from the cell of the range that is found by intersection of the row/column where the cell is and the passed range.

B.M · June 22, 2021, 12:03am

I’m not sure how to explain my problem any differently. For each row, I want a certain column’s calculation to involve multiplying two other columns together. I know I can just write plainly =SUM(INDEX( AllData ; ROW() ; 2) * INDEX( AllData; ROW(); 4), [other stuff]) but the “other stuff” is a bit involved and I was hoping I could shorten this into something simple like =SUM(GET_MY_DATA(), [other stuff]) so that it’s much more readable.

mikekaganski · June 22, 2021, 7:35am

Say, you are writing formula in column A, and need data from columns C and E, in addition to [other stuff]. So put this simple formula to A1, and drag-copy down:

=C:C*E:E+[other stuff]

It will work as I explaining - taking the values from the same row by intersection, without any complex functions - this is how it is designed.

karolus · June 21, 2021, 6:38am

No reason to create a UDF (User-Defined-Function),…use simply in Spreadsheet:

 =INDEX( AllData ; ROW() ; 2) * INDEX( AllData; ROW(); 4)