Results 1 to 4 of 4
Thread: Offset and Indirect (2003)

20080615, 14:45 #1
 Join Date
 Feb 2002
 Location
 A Magic Forest in Deepest, Darkest Kent
 Posts
 5,681
 Thanks
 0
 Thanked 1 Time in 1 Post
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>Jerry

20080615, 15:16 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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

20080615, 15:42 #3
 Join Date
 Feb 2002
 Location
 A Magic Forest in Deepest, Darkest Kent
 Posts
 5,681
 Thanks
 0
 Thanked 1 Time in 1 Post
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 itJerry

20080615, 15:50 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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