Sorting Alphabetically

User avatar
sobershea
2StarLounger
Posts: 184
Joined: 08 Feb 2010, 23:37
Location: Howell, Michigan USA

Sorting Alphabetically

Post by sobershea »

I have a long list of books read, sorted by author and then by series. I had named the 1st row of authors beginning with "A","A" and the first author beginning with B, "B' etc The problem is when you add a new book starting with A" the range does not expand by one. After adding many books, GOTO is messed up because the ranges didn't grow. So, GOTO J might take me to the F's because that is where J was originally.
I'm looking for direction. I've Googled but must not be saying it correctly as the results aren't what I need. Can anyone point me in the right direction? Or maybe Excel can't do it. :scratch:

TIA
Sherry

User avatar
HansV
Administrator
Posts: 78232
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Sorting Alphabetically

Post by HansV »

You could create dynamic named ranges.
Advantage: they will automatically adjust when you add or remove book titles.
Disadvantage: dynamic ranges aren't listed in the Go To dialog, so you have to type the names.

Remark: I wouldn't use A, B etc. as names - some single-letter names aren't allowed. In the English language version you can't name a range R or C since those letters stand for Row and Column, respectively, in R1C1 notation.

Let's say the author names are in column D.
Create a name for authors beginning with A. In the Refers To box, enter the formula

=INDEX('Sheet 1'!$D:$D, MATCH("A", 'Sheet 1"!$D:$D))

Replace Sheet 1 with the name of your book list sheet.
Similar for the other letters - simply replace "A" with "B", "C", etc.
Best wishes,
Hans

User avatar
sobershea
2StarLounger
Posts: 184
Joined: 08 Feb 2010, 23:37
Location: Howell, Michigan USA

Re: Sorting Alphabetically

Post by sobershea »

"Disadvantage: dynamic ranges aren't listed in the Go To dialog, so you have to type the names."

Thank you Hans. But how do I navigate to a specific range. Say I want to see what books I've read by James Patterson. How (where) do I enter Pp to get to the P's?

TIA
Sherry

User avatar
HansV
Administrator
Posts: 78232
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Sorting Alphabetically

Post by HansV »

In the Go To dialog (F5 or Ctrl+G), you can type Pp in the Reference box, then press Enter or click OK.
Best wishes,
Hans

User avatar
sobershea
2StarLounger
Posts: 184
Joined: 08 Feb 2010, 23:37
Location: Howell, Michigan USA

Re: Sorting Alphabetically

Post by sobershea »

When I name the range (aa, bb,etc), I am selecting only column A, not the entire database (A-F). When trying to Go To aa I get an error "Reference not valid". When I Go To bb, It takes me to the last row of aa. Why doesn't it Go To the first row of bb?
Sherry

User avatar
HansV
Administrator
Posts: 78232
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Sorting Alphabetically

Post by HansV »

What is the definition of aa? And of bb?
Best wishes,
Hans

User avatar
sobershea
2StarLounger
Posts: 184
Joined: 08 Feb 2010, 23:37
Location: Howell, Michigan USA

Re: Sorting Alphabetically

Post by sobershea »

aa =INDEX('List'!$A:$A, MATCH("A", 'List'!$A:$A))
bb =INDEX(List!$A:$A, MATCH("B", List!$A:$A))
Sherry

User avatar
HansV
Administrator
Posts: 78232
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Sorting Alphabetically

Post by HansV »

My apologies. Try these:

aa: =INDEX(List!$A$2:$A$1000000, MATCH("A*", List!$A$2:$A$1000000,0))
bb: =INDEX(List!$A$2:$A$1000000, MATCH("B*", List!$A$2:$A$1000000,0))
Etc.
Best wishes,
Hans

User avatar
sobershea
2StarLounger
Posts: 184
Joined: 08 Feb 2010, 23:37
Location: Howell, Michigan USA

Re: Sorting Alphabetically

Post by sobershea »

Ah, Ha! Works like a charm!

Thank You Hans.
:thankyou:
Sherry