We will be migrating from Ask to Discourse on the first week of August, read the details here

 Ask Your Question

# 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)

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

edit retag close merge delete

## 2 Answers

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

more

## Comments

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.

## Stats

Asked: 2020-06-24 16:03:13 +0200

Seen: 224 times

Last updated: Jun 24 '20