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

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)

\$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

Do you mean this?

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

Great, thanks, it works!

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)`.

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? .

thank you.
INDIRECT() is what i needed. Example from JohnSUN works.