Forum Discussion

Patrik De Paep's avatar
Patrik De Paep
Copper Contributor
May 26, 2017

ARRAY CONSTANTS, not working

buffer size     {150,200,300,400,500,650,700} Buffer 419 ltr   Hello, I entered my array constant in a single Excel Spreadsheet Cell, see above.   Then I "named" the cell (Excel/For...
Formulas & Functions
Need Help
Patrik De Paep's avatar
Patrik De Paep
Copper Contributor
May 29, 2017

Thanks Mourad,

 

In short the user can enter a list of buffer sizes. Currently this list is entered between brackets in one cell because I can never predict the number of different buffer sizes the user will enter. The moment that I use multiple cells: 1. in order to scoop with the unknown number of cells I will need to allow for an entire column ; 2. the layout of my spreadsheet is gone (but Ok is not that important)

 

So if there exists a formula to convert the text in between brackets entered in one cell into a matrixconstant this would rely help. In the English version of Excel you could name the cell and in the refers to section enter the EVALUATE formula. But this seems not to work in the Dutch version.

 

As an example I want to use the funtion =INDEX(NameOfNamedCell;3)

Or =INDEX($A$5;3)

 

Kind regards,

Patrik De Paep

Detlef_Lewin's avatar
Detlef_Lewin
Silver Contributor
May 30, 2017

Hello Patrik

 

You could use an Excel table for the buffer sizes. It expands automatically when a new number is entered. If less numbers are required then the user has to resize the table size manually. In either case the structured reference for the column is the same.

 

The formula I suggested above would be:

=LOOKUP(B1+AGGREGATE(15,6,ABS(tbl_Buffer[Size]-B1),1)*{-1;1},tbl_Buffer[Size])

 

 

Resources