How to assign multiple discontinuous cells to a LET parameter?

[Rnkhch]

Hello,

I know I can assign a single cell or a continuous range of cells to a LET parameter as below:

=LET(x,A1,operation...)
=LET(x,A1:M1,operation...)

But how would I assign multiple discontinuous cells to the x parameter? I'm hoping I can do something like this:

=LET(x,{A1;B6;D23;G12},operation...)

Thanks for any input!

Edit: I meant to say LET parameter in the title, but there is no way of editing it now. If possible, it would be great if one of the forum administrators could fix this; thank you

 

[jasonb75]

A method that I've used is
=LET(x,CHOOSE({1,2,3,4},A1;B6;D23;G12),operation...)

Although it may not work with everything.

 

[Rnkhch]

Yes, Thank you! I was experimenting with that.

Would it be possible to make a version of that, perhaps through LAMBDA or VBA, such that it would take a variable number of cells as input? (so that we don't have to modify the formula in two places and just select all the desired cells?) (might be complicated but would be really cool )

 

[Rnkhch]

By the way, as a related question, would it possible to write the "index_num" parameter of CHOOSE as a reference, INDIRECT, etc.? So far, from what I've tried, it needs to be literally spelled out as {1;2;3;4;etc.} and doesn't seem to accept any other format.

 

[jasonb75]

Would a named range suffice? You can name a non contiguous range of cells which should work for what you want to do as well.

The CHOOSE() method works with cells on different sheets, or even in different workbooks. I think that a named range will be limited to cells on the same sheet, but in all honesty I've never tried it beyond that.

edit:-

For the index param of CHOOSE() you could use the SEQUENCE function.

=LET(x,CHOOSE(SEQUENCE(4),A1;B6;D23;G12),operation...)

 

[Rnkhch]

Thank you. Yes, I tried names as well which work, as you mentioned. The inconvenience here would be that each time a new name should be defined. So, the CHOOSE method is more convenient in that sense.

 

[Rnkhch]

Thanks for the SEQUENCE suggestion! That makes the formula much better/easier to use and just one step away from an all-automatic function

Would it be possible to count the number of the entered "value" parameters of CHOOSE? It would be really cool if we could write something like this:

=LET(x,CHOOSE(SEQUENCE(count of all entered "value" parameters),A1;B6;D23;G12),operation...)

If possible, I think then we could wrap that LET function in a LAMBDA and have a great and convenient way of selecting discontinuous cells

 

[jasonb75]

Would it be possible to count the number of the entered "value" parameters of CHOOSE?

Not without Microsoft re-writing the code behind the function. All functions evaluate their arguments on a first to last / left to right basis, for it to do what you want it would have to evaluate the values in order to count them, then go back to the first parameter.

You can 'over' sequence, but then you would need to error trap the array to eliminate the excess. This would likely make things more complicated than a manual edit for that part.
CHOOSE(SEQUENCE(5);A1;B6;D23;G12))

would give you an array of 4 valid entries with a #VALUE! error for the last because the 5th value argument is empty.

 

[Rnkhch]

I see, thanks for the explanation. So I'll mark your post #5 as solution

 

[Rnkhch]

But I really like your over SEQUENCE suggestion though and started playing with the idea How can I specifically FILTER out only #VALUE! errors from the resulting array? And not any other errors that may happen based on contents of some cells?