calc - function to define cellrange

[DiesNuts]

INDIRECT(prefix & "0" & suffix & "." & myROWA & ":" & myROWA)~INDIRECT(prefix & "1" & suffix & "." & myROWA & ":" & myROWA)~INDIRECT(prefix & "2" & suffix & "." & myROWA & ":" & myROWA)

where

prefix: mySheet_
suffix: _A
myROWA: B

are stored in named cells on a sheet

Can I somehow turn this into a “lambda function”?

e.g. let’s say myROWA is stored in $E$11 what I want is

data3(E11)

which desugars into

INDIRECT(prefix & "0" & suffix & "." & B & ":" & B)~INDIRECT(prefix & "1" & suffix & "." & B & ":" & B)~INDIRECT(prefix & "2" & suffix & "." & B & ":" & B)

(because E11 stores B)

now if E12 stores F, then data3(E12) should desugar into

INDIRECT(prefix & "0" & suffix & "." & F & ":" & F)~INDIRECT(prefix & "1" & suffix & "." & F & ":" & F)~INDIRECT(prefix & "2" & suffix & "." & F & ":" & F)

[mark_t]

Your first expression where the column is defined in a named cell myROWA is possible using a named expression, but I think there is no way to pass an argument to a named expression so you would have to create the function data3 as a user defined function.

Unfortunately there is no way to pass cell ranges to user defined functions or to return cell ranges from a user defined function. Data from a cell range is converted to an array to pass to a function and an array of data can be returned from a function.

If you could give some information on how you plan to use the result of the expression it might be possible to help create a user defined function. For example if you plan to use =SUM(data3(E11)) then the data3 function could return an array of the data from the three sheets.

[DiesNuts]

… Why can I not edit my question to include the large formula that is too long for comments?

Anyway, consider, for example, that I have data on different sheets. I want to remove the first and last so-and-so-many entries and do some calculation - e.g. percentiles - on the rest. The formula will look something like this (continued over the next few comments):

[DiesNuts]

=PERCENTILE(OFFSET(INDIRECT(prefix & "0" & suffix & "." & F$12 & ":" & F$12),2+dataoff_,0,COUNT(INDIRECT(prefix & "0" & suffix & "." & F$12 & ":" & F$12))-dataoff_-3,1)~

[DiesNuts]

OFFSET(INDIRECT(prefix & "1" & suffix & "." & F$12 & ":" & F$12),2+dataoff_,0,COUNT(INDIRECT(prefix & "1" & suffix & "." & F$12 & ":" & F$12))-dataoff_-3,1)~

[DiesNuts]

OFFSET(INDIRECT(prefix & "2" & suffix & "." & F$12 & ":" & F$12),2+dataoff_,0,COUNT(INDIRECT(prefix & "2" & suffix & "." & F$12 & ":" & F$12))-dataoff_-3,1),$F5)

[DiesNuts]

or another application would be where I have data on multiple columns on the same sheet, e.g. on columns G N U AJ AQ AX BM BT CA and I want to do the same to it, e.g. =PERCENTILE(OFFSET(G:G,start_,0,stop_-start_,1)~OFFSET(N:N,start_,0,stop_-start_,1)~OFFSET(U:U,start_,0,stop_-start_,1)~OFFSET(AJ:AJ,start_,0,stop_-start_,1)~

[DiesNuts]

OFFSET(AQ:AQ,start_,0,stop_-start_,1)~OFFSET(AX:AX,start_,0,stop_-start_,1)~OFFSET(BM:BM,start_,0,stop_-start_,1)~OFFSET(BT:BT,start_,0,stop_-start_,1)~OFFSET(CA:CA,start_,0,stop_-start_,1),$A46)

[DiesNuts]

Fortunately, I can then drag this to the right to offset the range, but still, this is really, really inconvenient.