How to use cell value as variable in formula TEXTJOIN range?

[gorobic]

Hi. I use next formula a lot of times to concatenate values:

=TEXTJOIN(" | ";0;$variations.B2:B15)

in next cell i use:

=TEXTJOIN(" | ";0;$variations.C2:C15)

and so about 10 times.

$variations is another sheet. I use it to add structured information and then concatenate it in a single field, in main sheet, for import on the site.

In the main sheet I have a list of apartments. In the variation sheet I have the list of variations of each apartment. Row 2:15 for apartment 1; row 16:22 for apartment 2; etc.

In main sheet i need to have a lot of apartments. Each apartment has an interval in the variation sheet.
For apartment 2 i need do write:

=TEXTJOIN(" | ";0;$variations.B16:B22)

=TEXTJOIN(" | ";0;$variations.C16:C22)

so I will change about 10 times for dozens of apartments.

QUESTION:

Can i use value of cell from main sheet inside formula, to indicate the interval? Example:
in main sheet i will have cell B2 with value 2 and cell C2 with value 15

Formula: =TEXTJOIN(" | ";0;$variations.B (B2):B (C2)) … equivalent of $variations.B2:B15

After that i will have:
B3 = 16; C3 = 22

And formula: =TEXTJOIN(" | ";0;$variations.B (B3):B (C3))

etc.

Hot to export this number (noted in bold in the example above), to use it as a simpler way to add a lot of info?

Thx

[JohnSUN]

Do you mean this?

=TEXTJOIN(" | ";0;INDIRECT("$variations.B"& $B$2 &":B" & $C$2 ))

[gorobic]

Great, thanks, it works!

[Lupp]

I feel to not have completely understood, but
As with any reference you need to pass to any expression, you can use one of the functions INDIRECT() or OFFSET() to calculate the range. (You can also pass calculated references to a validation CellRange, but not to a Chart’s DataRanges.)
Read the help for INDIRECT() and OFFSET().
OFFSET($variations.$A$1; $B$3 - 1; 1; $C$3-$B$3+1; 1) e.g. will return the reference to the range $variations.$B$2:$B$22).

[Lupp]

Please also consider what I posted here: Why do I often get an error (508, 504, 502 e.g.) if I paste a Calc formula from some post into my sheet? .

[gorobic]

thank you.
INDIRECT() is what i needed. Example from JohnSUN works.
But thank you for your answer

[Lupp]

Example from JohnSUN works

Yes. Of course.
However, there are many related cases where OFFSET() is more flexible. I use both mentioned functions to create calculated references, depending on the case. You may also want to know.