Ask Your Question
0

calc - function to define cellrange

asked 2016-11-20 17:14:08 +0200

DiesNuts gravatar image
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)
edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2016-11-21 17:09:24 +0200

mark_t gravatar image

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.

edit flag offensive delete link more

Comments

.. 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 gravatar imageDiesNuts ( 2016-11-25 07:12:47 +0200 )edit

=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 gravatar imageDiesNuts ( 2016-11-25 07:13:02 +0200 )edit

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 gravatar imageDiesNuts ( 2016-11-25 07:13:28 +0200 )edit

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 gravatar imageDiesNuts ( 2016-11-25 07:13:44 +0200 )edit

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 gravatar imageDiesNuts ( 2016-11-25 07:22:13 +0200 )edit

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 gravatar imageDiesNuts ( 2016-11-25 07:23:14 +0200 )edit

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

DiesNuts gravatar imageDiesNuts ( 2016-11-25 07:25:30 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-11-20 17:14:08 +0200

Seen: 241 times

Last updated: Nov 21 '16