Ask Your Question

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

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

gorobic gravatar image

updated 2020-06-24 16:04:26 +0200

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.


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


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?


edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2020-06-24 16:19:13 +0200

JohnSUN gravatar image

Do you mean this?

=TEXTJOIN(" | ";0;INDIRECT("$variations.B"& $B$2 &":B" & $C$2 ))
edit flag offensive delete link more


Great, thanks, it works!

gorobic gravatar imagegorobic ( 2020-06-24 16:53:23 +0200 )edit

answered 2020-06-24 16:32:24 +0200

Lupp gravatar image

updated 2020-06-24 16:33:36 +0200

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

edit flag offensive delete link more


Please also consider what I posted here: .

Lupp gravatar imageLupp ( 2020-06-24 16:36:31 +0200 )edit

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

gorobic gravatar imagegorobic ( 2020-06-24 17:30:20 +0200 )edit

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.

Lupp gravatar imageLupp ( 2020-06-24 22:09:41 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


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

Seen: 33 times

Last updated: Jun 24