The question could also apply to a larger data set ( i.e., multiple columns ).
But the goal is the same: to concatenate a range of cells defined by two variables.
Say B1=5 and C1=2. Do you need in A1: “52” as text? Use =B1&C1? Or if as number, use =NUMBERVALUE(B1&C1).
Add Answer is reserved for solutions. Please, click edit below your question to add more information.
Per the question, in the simple example, I want to concatenate the range of Cells in Column A between the Cell number stored in B1 and the Cell number stored in C1, rather than concatenating Cells B1 and C1. In the more complex version, if I had a data range of A1:Z1000, and AA1=20 and BB1=49, how would I select a range such as C(AA1):Q(BB1), where AA1 and BB1 are variables? If I knew the values beforehand, I would simply =CONCAT(C20:Q49) to obtain my output. But the values stored in AA1 and BB1 are variables (whole numbers) derived elsewhere (not necessarily the same Sheet or even the same Workbook.
The appropriate way to reference the range is
=Offset($A$1;B1-1;0;C1-B1+1;1).
If you need to get an address for the range by concatenating strings, you can use
=ADDRESS(B1;COLUMN($A$1);4) & ":" & ADDRESS(C1;COLUMN($A$1);4).
To reference the range by
=INDIRECT(ADDRESS(B1;COLUMN($A$1);4) & ":" & ADDRESS(C1;COLUMN($A$1);4))
is needlessly complicated.
Concerning the usage of the reference returned by OFFSET() or by INDIRECT() you need to regard that they shouldn’t be used to pull CellRange contents to a target range. The needed locking of the output range would cause problems. Al referencing depending indirectly on editable or calculated values should only be used in formulas needing access to variable cell ranges as input for their calculations.
Sorry, but I don’t understand how to implement your proposed solution(s). How does the =offset function know where the target Cell is to begin and end the Range, the same question applies to the =address function. And in either case, what would the syntax be to use that information in the =CONCAT function?
OFFSET() calculates a reference from its arguments, read the help. You can feed the calculated reference result to CONCAT(), like
=CONCAT(OFFSET($A$1;B1-1;0;C1-B1+1;1))Did you resd the OFFSET() help? A reference point (cell) for offset is given by its first parameter. The next two of them tell how many steps to go down / right from there to find the top left cell of the referenced range. Then come two parameters telling how many rows / columns to include with the refenence.
The other functions occurring in my answer also have help pages.
What read “How do I concatenate the range A(B1):A(C1)” in your question, I understood as
“How do I concatenate the range address A(B1):A(C1)”. (There was no unambiguos meaning.)
Anyway, I showed you two ways to generate the reference you need. Using the first one, you can, of course, enter the formula =CONCAT(OFFSET($A$1;B1-1;0;C1-B1+1;1)) to get the values from the respective cells concatenated (Without a delimiter!).
Way cool! Didn’t realize that you could nest functions like that. Didn’t initially look at the LibreOffice Help Section ( hidden under the “Discover” header ); went straight to the “Get Help” header and landed in the Community Assistance Forum. In any event, problem solved! How do I give a big “Thumbs Up” to all and mark the question as “Answered”?
There’a checkmark in a gray circle next to the header of the answer (left). If you click on it once, the answer is marked “accepted”.
To see the effect (gray turning to green you may need to refresh the page.