Results 1 to 12 of 12
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have been using the following ARRAY formula to give me a string of the first and last reference in a range that had blank cells between each item.

    The range was I19, I21 ......... I37.

    ="REFS "&IF(I19<>"",I19,"")&IF(MAX((I21:I37<>"")*ROW(I21: I37)*(MOD(ROW(I21:I37),2)=1))>0,"-"&INDEX(I1:I37,MAX((I21:I37<>"")*ROW(I21:I37)*(MOD (ROW(I21:I37),2)=1))),"")

    This was an array formula.

    My range has now changed to C23:C42 as a continuous range (without blanks). How do I change the formula?

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    ="REFS "&C23&" - "&C42

  3. #3
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sorry, I should have been more specific. I need it to find the last item in the range, excluding &nbsp, which is in all cells that would otherwise be blank. The required range may be C23:C23, C23:C35, C23:C42, depending on how many items are available.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    As an array formula:

    ="REFS "&C23&" - "&INDEX(C1:C42,MAX((C23:C42<>"")*ROW(C23:C42)) )

  5. #5

  6. #6
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hans, If there is only one row of data in the range of 20 rows, my result is xxxxxx-xxxxxx. Can this be modified to drop the -xxxxxx, as there is only one item to report. ?

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Again as an array formula:

    ="REFS "&C23&IF(MAX((C23:C42<>"")*ROW(C23:C42))=23,"" ," - "&INDEX(C1:C42,MAX((C23:C42<>"")*ROW(C23:C42)) ))

    If C23 could also be you'd need to check for that too.

  8. #8
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Many Thanks, that works.

    Being really petty now, this really is not that important:

    If one item, REF instead of REFS ? No worries if not worth bothering with...

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You should be able to work that out for yourself now. You've got the formula, you only need to shift the S into the IF function.

  10. #10
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='775641' date='18-May-2009 00:16']You only need to shift the S into the IF function.[/quote]
    Well I'm baffled, as I can't see how it is that simple....

    If the required result is split into 3 sections:
    1: REF

    and,

    2: _xxxxxx

    or,

    3: S_xxxxxx-xxxxxx

    where _ = space.

    I can't for the life of me see how to do that?

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    ="REF"&IF(MAX((C23:C42<>"")*ROW(C23:C42))=23,"","S ")&" "&C23&IF(MAX((C23:C42<>"")*ROW(C23:C42))=23,"" ," - "&INDEX(C1:C42,MAX((C23:C42<>"")*ROW(C23:C42)) ))

    (as an array formula)

  12. #12
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='775736' date='18-May-2009 17:54']="REF"&IF(MAX((C23:C42<>"")*ROW(C23:C42))=23,"","S ")&" "&C23&IF(MAX((C23:C42<>"")*ROW(C23:C42))=23,"" ," - "&INDEX(C1:C42,MAX((C23:C42<>"")*ROW(C23:C42)) ))

    (as an array formula)[/quote]

    No, I defo would not have figured that out! Maybe I am in the wrong job. Thanks Hans.

Posting Permissions

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