LAMBDA problem

[MrSweck]

Having trouble with creating a LAMBDA function. I create a LET formula and make a =LAMBDA() function out of it. But I would also like a certain cell reference as part of the tool. Same position/size for use in every sheet, not supposed to be touched by the user (kind of an 'ActiveSheet.Range("$A$1:$A$200"') in VBA code). But when I save the LET formula as a LAMBDA function it automatically converts that "hidden" reference into a 'OnlyThatSheetName!$A$1'-type of reference, making the function useless in other sheets.

The formula for it:

=LET(
somr,Conv!C1:C890, <--(named reference open for user in LAMBDA)
htjomr,HtjAlen!A1:A2500, <--(named reference open for user in LAMBDA)
brukomr,C1:C200, <--(the one that turns into SpecifikSheetName!C1:C200 when saved in LAMBDA)
resomr,OFFSET(somr,,1),
htjres,OFFSET(htjomr,,1),
rownum,COUNTA(brukomr),
seq,SEQUENCE(rownum-1,,2),
ind,INDEX(brukomr,seq),
person,XLOOKUP(ind,somr,resomr,"N/A"),
CHOOSE({1/2},person,XLOOKUP(RIGHT(person,11)&" Sum",SUBSTITUTE(htjomr,"+","-"),htjres,""
)))

It's supposed to be used in a designated column in the table, so if there is some sort of "offset from formula cell" solution possible to reach info from another column in the same sheet, that will also work. Anyone have a trick for this in the backpocket... or anyone want to tell me that it's not possible (so I can put it aside)?

(And before suggestions about using things like table objects and named columns etc... the design of the sheet isn't mine to mess with, so that's not possible.)

Regards,
/Nick

 

[Fluff]

Hi & welcome to MrExcel.
Why not let the user specify the range, that way it will be the active sheet.

 

[MrSweck]

Hi & welcome to MrExcel.
Why not let the user specify the range, that way it will be the active sheet.

That is the plan B (that makes it work at all). However, I would like the user to not do something that is already done and then do it wrong. Another problem is that this C column sometimes comes hidden to the user which opens up for the "selecting third visible column" mistake. If it can be avoided, that would be the optimal.

 

[Fluff]

I very much doubt it is possible. If there was no sheet name then xl would not know which sheet to use & if it used the active sheet then all the other sheets would use the wrong data. Especially as the formula is volatile.

 

[Fluff]

Another problem is that this C column sometimes comes hidden to the user

That is not a problem, the user simply types C1:C200 into the function, they do not need to select the cells.
You can also put a comment to that effect in the comments box of the defined name.

 

[MrSweck]

That is not a problem, the user simply types C1:C200 into the function, they do not need to select the cells.
You can also put a comment to that effect in the comments box of the defined name.

I thought about that myself... anything and everything possible to simplify. Chosing C:C will work just as fine and it will be both easier to type and easier to actually SEE if selecting, since it will mean clicking on an actual 'C' character. I also noticed something now that can be used for an "internal" check within the formula. If a COUNTA in the column offset 2 steps to the left of the chosen one is equal to a COUNTA in a column 3 steps to the left, it will be the wrong column, whether it's B or D that has been specified... and I could let it say something (instead of risking getting the wrong info).

 

[MrSweck]

I got a tip today from another guy that solved this. Instead of specify the reference as 'C1:C200' specify it as =INDIRECT("C1:C200").
This tricked the Name Manager and suddenly it was the activesheet reference I wanted.

 

[Fluff]

Glad you sorted it & thanks for letting us know.

 

[MrSweck]

Glad you sorted it & thanks for letting us know.

I instantly started to think of other places where I could do the same thing, so I'm sure it's not something that I'm the ONLY one who it could be worth something to.