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!)
Bookmarks