Novice Needs Help

  • Thread starter Thread starter Aaron
  • Start date Start date
A

Aaron

I need help with a listbox. I have figured out how to
add a listbox to a worksheet from the Forms toolbar and
how to specify the input range. I want to be able to
link the value selected from the list to a particular
cell. The cell link simply puts a number in the linked
cell corresponding to where it falls in the list. How can
I link the value selected in the list of a cell?

Second question - Can I use the autocomplete function in
the listbox. I have autocomplete selected for the
worksheet in the options menu. I have a list of names
that are in alphabetical order. I want to be able to
start typing 'J' for instance and get to the names
beginning with 'J' and select from there.

Thanks in advance for any help you can provide.
 
Aaron,
If you had a set up like this:
Sheet 1: A1 is linked to the list box (shows a number)
Sheet 2 (Tables) contains names running down column C, location in column D,
and salary in E.

This formula will lookup the location:

=VLOOKUP(INDEX('Tables'!$C$2:$C$142,$A$1,1),'Tables'!$C$2:$E$142,COLUMN(D1),
FALSE)
Change COLUMN(D1) to COLUMN(E1) to lookup the salary. (the 1 could be any
number)
A combo box will perform the autocomplete function you mention. Maybe
consider switching controls?
HTH
Chris
 
Chris, thanks for the help! This works great.

I have one other question. I don't know whether the
autocomplete works on the combo box yet. Here is why -
if I position the combo box in a particular cell (say
A5), and I tab to that cell and start to type, I am just
typing in A5. I am not typing in the combo box. Does
that make sense. How do I 'tie' the combo box to the
cell?

thanks again.
 
There's actually two different ways:
1) If the combobox is the one found on the "Forms" toolbar, right-click it
and select "Format Control". Put $A$5 in the "Cell Link" space. You can
either use the selector, or just type it in.
2) If it's the one from the "Controls Toolbox" toolbar, you'll need to get
into design mode. In the menu, select View-Toolbars-Control Toolbox to
make the toolbar visible (if nec). Now click the icon with the blue
triangle (it's usually the first one.) When in design mode, hovering the
mouse pointer over that icon will say "Exit Design Mode" otherwise it says
"Design Mode". Now click on your combobox (you should see the 'handles' on
it now). Now click the second icon in "Controls Toolbox"- it has a hand
pointing at a list. A Properties window will open. Scroll to the property
called "LinkedCell" and type in $A$5 and press Enter. Next click on the
Design Mode icon again to exit design mode. You can close the properties
window and any toolbars that are open.

Once you've done these steps, it should work for you. You can use the
properties window (or Format Control) to change other aspects of the
combobox as well. You might want to experiment with it a bit.

HTH
Chris
 
Back
Top