Sub divide cells without adding columns

Question

The set up does not lend itself to adding 26 columns for this or to expand a

column for this task.  I can merge a row to span 14 columns.  The list starts on row

33 just below the freeze set to X33.

I have a long list of plants sorted alphabetically.  I would like to place an index

on which I can click to scroll to that section of the plants. 

For example, along the top of the chart I would like to see the alphabet and be

able to click on a letter to scroll the chart to plants starting with that letter. 

Click on D and the list scrolls to have Daffodils start at Y33 followed by all the

rest of the plants.

Answer 1

Hello,

one other possibility, no code, works in Excel 2013 and up:

• Turn your list into a table (Insert Table)
• add a column that has the letter index. You could use a =LEFT(Title,1) for that
• Insert a slicer for the index column
• format the slicer to have 26 columns and make it wide enough to show all the letters.
• position on top of the list.
• regardless of the sort order of the list, one click on the slicer will return all titles that start with that letter. You can also Shift-Click to select contiguous letters or Ctrl-click to select non-contiguous letters. 

Answer 2

>> Will I be able to control the size of these buttons?

These "buttons" are as wide as they need to be to show the text they need to show. Think dynamic sizing.

>> Can I set it to two rows of letters A-M, N-A?

You can control the number of columns on the slicer. By default a slicer is 1 column wide and shows all values in rows. 

You can set how many columns the slicer has. If you want A to M then set it to 13 columns, and everything after M will be plotted in the second row.

Answer 3

Two ideas worth the merit to try.  It does spark an adjustment though.  I could place the letters in a column above the freeze.  All I would need is the code to scroll the list.  I will see if anything suggested will work this way.

Answer 4

Howard Kittle method. . . . .

Sort your validation list and at the beginning

of the A's enter a single A, do the same for the beginning of the B's and

all the way through to the Z's.

Click in the cell that has the drop down validation and type in the letter

of the list you desire... say you type in an O.  DO NOT hit enter... leave

the cell in the edit mode (still selected) and now hit your down arrow.

Should take you to the O which is the top of the O list of selections.

Alternate method with VBA. . . . . . . .    

Add a ComboBox which has autocomplete functionality.

See Debra Dalgleish's site for instructions and a sample workbook.

http://www.contextures.on.ca/xlDataVal10.html

Gord

Answer 5

Just a top-of-the-head thought that you might find usable... put 26 shapes (squares or circles) next to each other horizontally in the frozen set of rows, put a letter in each and have each shape's macro scroll to the appropriate position for its letter. Since the shapes float on top of the grid, you can resize the column widths as needed without affecting the shapes with the letters.