# Thread: Offset and Indirect (2003)

1. ## Offset and Indirect (2003)

This little Sunday afternoon exercise involves the idea of returning a string value from a column of letters. This was aftewr a discussion in Scuttlebutt about hop codes

<table border=1><td>1</td><td>A</td><td>2</td><td>B</td><td>3</td><td>C</td><td>4</td><td>D</td><td>5</td><td>E</td><td>...</td><td>...</td></table>

The values in the second column are arbitory for this part of the development.

I have named the range data and then in Cell G3 typed the formula ="B"&F1. In F1 I will be typing a value (say 2) to return a string B2 to be used in the Indirect formula below

I want the workbook to return a string from the indirect cell value and concatanate it with the 4 cells values below it and have come up with this rather cumbersome formula.

=INDIRECT(\$G\$1)&OFFSET(INDIRECT(\$G\$1),1,0)&OFFSET( INDIRECT(\$G\$1),2,0)&OFFSET(INDIRECT(\$G\$1),3,0)&OFF SET(INDIRECT(\$G\$1),4,0)

This works fine but was wondering if someone can think of another, less prolixit, formula <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

2. ## Re: Offset and Indirect (2003)

If you can stand intermediate columns you can put in C1:
=B1

and in C2:
=C1&B2

And Copy C2 down the column...

Then you can use the shorter:
=SUBSTITUTE(OFFSET(INDIRECT(\$G\$1),<font color=red>4</font color=red>,1),IF(F1=1,"",OFFSET(INDIRECT(\$G\$1),-1,1)),"")

The formula could be shortened if one always new each cell would have 1 letter or all the same number of letters (I presumed neither had to be true). [In either of those cases RIGHT could be used instead of substitute and you would not need the IF of the 2nd Offset/Indirect...]

To me the advantage of my formula is that by changing the "4", you can get as many or as few as you like to concatenate, without having to modify the formula. [The "4" can be put into a cell...

The multiple concatenations in your formula makes its (for me) cumbersome. Using the intermediate column which is a simple copy, alleviates it.

Steve

3. ## Re: Offset and Indirect (2003)

Thanks Steve

That does make sense, nice to see another approach as I am not a great user of the INDIRECT function so have no real experience of it

4. ## Re: Offset and Indirect (2003)

The real problem with your question is one of my excel peeves: the inability to concantenate a range...

The SUM/Concatenate functions are not completely analogous
To Sum A1 through A10
=A1+A2+A3 + A4 + A5 + A6 + A7 + A8 + A9 + A10
=Sum(A1,A2,A3,A4,A5,A6,A7,A8,A9,A10)
=Sum(A1:A10)

But to concatenate A1- A10
=A1& A2 & A3 & A4 & A5 & A6 & A7 & A8 & A9 & A10
=CONCATENATE(A1,A2,A3,A4,A5,A6,A7,A8,A9,A10)

But this is can NOT be done with :
=CONCATENATE(A1:A10)

Concatenation must be done individually in Excel. The Intermediate column is a faster creation with many concatenations since it is a copy and not expanding the formula...

Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•