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 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?

CodePudding user response：

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.

CodePudding user response：

That might help

=TEXTJOIN("",1,REGEXEXTRACT(D4, "\$(. ?)\$(. )"))

CodePudding user response：

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 raisin rewritten as

NAMED_GREATER(cell, cell2)

with formula:

=cell>cell2*8

Where that is not feasible, You can also use INDIRECT, but OFFSET in this answer is preferred, when possible, because unlike string ranges, they auto update based on changes.

INDIRECT("$D$4")