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.
TIA
Sorting Alphabetically
-
- 2StarLounger
- Posts: 184
- Joined: 08 Feb 2010, 23:37
- Location: Howell, Michigan USA
Sorting Alphabetically
Sherry
-
- Administrator
- Posts: 78483
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Sorting Alphabetically
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.
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
Hans
-
- 2StarLounger
- Posts: 184
- Joined: 08 Feb 2010, 23:37
- Location: Howell, Michigan USA
Re: Sorting Alphabetically
"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
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
-
- Administrator
- Posts: 78483
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Sorting Alphabetically
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
Hans
-
- 2StarLounger
- Posts: 184
- Joined: 08 Feb 2010, 23:37
- Location: Howell, Michigan USA
Re: Sorting Alphabetically
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
-
- Administrator
- Posts: 78483
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- 2StarLounger
- Posts: 184
- Joined: 08 Feb 2010, 23:37
- Location: Howell, Michigan USA
Re: Sorting Alphabetically
aa =INDEX('List'!$A:$A, MATCH("A", 'List'!$A:$A))
bb =INDEX(List!$A:$A, MATCH("B", List!$A:$A))
bb =INDEX(List!$A:$A, MATCH("B", List!$A:$A))
Sherry
-
- Administrator
- Posts: 78483
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Sorting Alphabetically
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.
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
Hans
-
- 2StarLounger
- Posts: 184
- Joined: 08 Feb 2010, 23:37
- Location: Howell, Michigan USA