named ranges defined by first letter

[ajm]

has anyone ever created named ranges based on the first letter of each item in a long list? so all items starting with "A" would be in the "A" named range, all items starting with "B" would be in the named range "B", etc. one of my favourite formulas is this one for capturing dynamic lists :
=$A$2:INDEX($A:$A,COUNTA($A:$A))
I thought that maybe i could change out the COUNTA for a countif but realised that this would only give me a named range staring at A2 and containing only that number of entries that start with A. fine for A, but then to create the B named range, i would end up with a list as long as the count of entries that start with B, but from A2.

Any ideas?

 

[StephenCrump]

I have no idea why you'd want to do this. But assuming MyList: =Sheet1!$A$2:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A))

Try: =INDEX(Sheet1!$A:$A,SMALL(IF(LEFT(MyList,1)="A",ROW(MyList)),ROW(INDIRECT("1:"&SUMPRODUCT(--(LEFT(MyList,1)="A"))))))

Or if you have Excel 365: =FILTER(MyList,LEFT(MyList,1)="A")

(You won't be able to create C or R)

 

[ajm]

I have no idea why you'd want to do this. But assuming MyList: =Sheet1!$A$2:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A))

Try: =INDEX(Sheet1!$A:$A,SMALL(IF(LEFT(MyList,1)="A",ROW(MyList)),ROW(INDIRECT("1:"&SUMPRODUCT(--(LEFT(MyList,1)="A"))))))

Or if you have Excel 365: =FILTER(MyList,LEFT(MyList,1)="A")

(You won't be able to create C or R)

Thanks Stephen. why? good question. Trying to help someone out. They have a long list of names and I am trying to find an easy way for them to navigate it so that they can make selections from it. I am using an active x list box currently but this looks too messy. thought if i could have a bunch of letter buttons they could pick a letter, ...

 

[ajm]

now just need to figure out how to bring the first entry with each new letter to the top of the list box. in simpler terms, the list starts at Axxxx, Ayyyy, (or whatever) and a user wants to see the names beginning with T, when you type T, the first T entry is at the bottom of the list box. any ideas?