How to create array dynamically from a string without copy pasting?-CodePudding

I have these as strings: { column01 \ column02 \ column01 }(for other countries { column01 , column02 , column01 }). I want them evaluated as a array as if copy pasted.

The array string range is created automatically, based on the selection of the user. I created a dynamically personalized dataset based on a sheet studeertijden named. The user can easily select the wanted tables by checkboxes, so by Google Sheets ARRAY range (formula). I try to copy these content to an other sheet ... to make the required data available for Google Data Studio.

The contents of page studeertijden is NOT important. Let's say, a cell in 'legende-readme'!B39 returns a string with the required columns/data in a format like this:

{ studeertijden!A:A \ studeertijden!B:B}

If I put this in an empty sheet, by copy and paste, it works fine :

={ studeertijden!A:A \ studeertijden!B:B}

How can it be done automatically???
my first thought was by indirect ...

What I've tried(Does NOT work):

Cell 'legende - readme'!B39 contains:

{ studeertijden!A:A \ studeertijden!B:B}

=indirect('legende - readme'!B39)
returns :

#REF! - It is not a valid cell/range reference.

={ indirect('legende - readme'!B39) } returns :

#REF! - It is not a valid cell/range reference.

={'legende - readme'!B39}
returns : { studeertijden!A:A \ studeertijden!B:B}

Note : For European users, use a '\' [backslash] as the column separator. Instead of the ',' [comma].

CodePudding user response：

Assuming I've understood the question, if string doesn't need to start and end with curly brackets, then is this the behaviour you are looking for?

=arrayformula(transpose(split(byrow(transpose(split(string,",")),lambda(row,join(",",indirect(row)))),",")))

N.B. In my case I'm assuming that string is of the format 'studeertijden!A:A,studeertijden!B:B' (i.e. comma separated). So SPLIT by the comma to generate a column vector of references, TRANSPOSE to a row vector, INDIRECT each row (with the JOIN to return a single cell per row), ARRAYFORMULA/SPLIT to get back to multiple cells per row, TRANSPOSE back into columns like the original data.

This would be a lot easier if BYROW/BYCOL could return a matrix rather than being limited to just a row/column vector - the outer SPLIT and the JOIN in the BYROW wouldn't be needed. Over in Excel world they can also use arrays of thunks rather than string manipulation to deal with this limitation (which Excel also has), but Google Sheets doesn't seem to allow them when I've tried - see https://www.flexyourdata.com/blog/what-is-a-thunk-in-an-excel-lambda-function/ for more details.

CodePudding user response：

={"1" , "2"}

={"1" \ "2"}

both are valid. it all depends on your locale settings

see: https://stackoverflow.com/a/73767720/5632629

with indirect it would be:

=INDIRECT("sheet1!A:B")

where you can build it dynamically for example:

=INDIRECT("sheet1!"& A1 &":"& B1)

where A1 contains a string like A or A1 (and same for B1)

another way how to construct range is with ADDRESS like:

=INDIRECT(ADDRESS(1; 2)

from another sheet it could be:

=INDIRECT(ADDRESS(1; 2;;; "sheet2")

or like:

=INDIRECT("sheet2!"&ADDRESS(1; 2))

for a range we can do:

=INDIRECT("sheet2!"&ADDRESS(1; 2)&":"&ADDRESS(10; 3))