Results 1 to 4 of 4
  1. #1
    Platinum Lounger
    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

  2. #2
    WS Lounge VIP sdckapr's Avatar
    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

  3. #3
    Platinum Lounger
    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 it
    Jerry

  4. #4
    WS Lounge VIP sdckapr's Avatar
    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

Posting Permissions

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