# 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

edit retag close merge delete

Sort by » oldest newest most voted Do you mean this?

=TEXTJOIN(" | ";0;INDIRECT("$variations.B"&$B$2 &":B" &$C$2 ))  more ## Comments 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).

more

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

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.