Results 1 to 9 of 9

Array formulas to create a sub list - a problem I can't fix

Threaded View

  1. #1
    Registered User
    Join Date
    04-14-2013
    Location
    Leeds, England
    MS-Off Ver
    Excel 2007
    Posts
    14

    Question Array formulas to create a sub list - a problem I can't fix

    I have attached a spreadsheet which is trying to extract names from a sorted list when you select the first letter. Credit is not mine, I copied the idea from a website but cannot find it to credit the original author!

    $A1:$A1000 holds a list of names sorted alphabetically.
    $B1 is either emply or you can select letters A to Z from the drop down list.

    $D1:$D32 contain the following array formula to list only those names in $A1:$A1000 that start with the letter.

    {=OFFSET(INDIRECT(ADDRESS(MATCH($B$1 & "*",$A1:$A1000,0),1)),0,0,COUNTIF($A1:$A1000,$B$1 & "*"),1)}

    If $B1 is blank, contains a letter which matches no entries or contains a letter matching >1 entry it works exactly as I want. #N/A are there to signify empty values.

    However, if I seelct a letter which returns only 1 name, the name is repeated in all 32 cells.

    When letter S,Y or Z is selected (which match 2 rows) the ADDRESS is returning $A3:$A4, $A5:$A6 and $A7:$A8 respectively which is what I want.

    If C or J is selected ADDRESS is returning $A1 and $A2 respectively.

    I'm assuming that is the cause of my problem. If I try and hard code INDIRECT($A1:$A1) it makes no difference!!!

    Any help would be greatly appreciated... (I'm at the limit of my array formula knowledge!)
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1