Can I make a named function in google sheets that will reference a cell in the sheet it is used in

Question

I am making a named function that needs to reference a cell in the sheet and whenever I make it and type for instance $D$4 it converts it to 'SheetName'!$D$4(First sheet's D4) making it to where I can't use the named function in other sheets.

Is there a way I can make it look at the D4 in the current sheet or do I need to manually make a named function for every sheet that I use it in?

Answer 1

I was able to figure out a work around. Basically, instead of directly getting the cells, I used offsets to get those cells for me. For the reference to the offset, I used an argument which I always set to A1. EX: "OFFSET(CELL, 3, 3)" where CELL is the argument set to A1, would give me D4 in that specific sheet.

Answer 2

Best way is to pass whatever range is needed as a argument. For eg, a function named NAMED_GREATER(cell) with definition =(cell>D4*8) can be rewritten as

NAMED_GREATER(cell, cell2)

with formula definition:

=cell>cell2*8

Where that is not feasible, You can use OFFSET in this answer, when possible. But if OFFSET isn't feasible, use INDIRECT

INDIRECT("$D$4")

This makes D4 refer to the current sheet's D4. However, OFFSET is preferred, because unlike string ranges, they auto update based on changes.