Drop down menu?

[valintine]

Sorry in advance for this probably easy question, but could anyone let me in on the secret of linking the contents of a drop down menu with the input of a fomula. IE. I know how to create a drop down menu, but I can't figure out how to lik the information in the menu to a cell/formula. For example: if the menu has a 1 selected, I want the formula to use this number, or if the menue has a 5 or 10, or any number with the menu selected, use this number!

Thanks in advance,
Valintine.

 

[Guidoo]

I assume you used the "listbox" from the Control Toolbox in Excel. When you are in the "design mode" and right click on the listbox, you can select "properties". One of the properties is "LinkedCell". The selected item will be entered into this linked cell.

 

[valintine]

Oh OK, and after I do this I assume I reference the formula to the new cell that the menu is linked to?

Thanks!
Valintine.

 

[Guidoo]

Correct. I normally link to a cell that is hidden behind the listbox, but this is not required.

 

[valintine]

Thanks alot Guidoo, I have another question to bother you with.

Can I setup the list box so that when a perticular cell has a certian number in it the list box gives me a certian range of options?

Forinstance, if a cell has a number 1 in it, the list box has say a list of cars in it. Or if the cell has a number 2 in it, the list box has a list of countrys etc.?

Any help would again be appriciated!

Valintine.

 

[Guidoo]

Valentine,

Yes, that is possible. I will use an example:

For your listbox, you have a ListFillRange selected of say A1:A5.
In cell A1, you have the formula:
=IF($B$1=1,C1,D1)
In cell C1 you have "Ford"
In cell D1 you have "France"

In cell A2, you have the formula:
=IF($B$1=1,C2,D2)
In cell C2 you have "Volkswagen"
In cell D2 you have "Germany"

and similar for cells A3..A5

So when you have 1 entered in cell B1, you will get the cars in your listbox. For every other value in B1, you will get the countries in your listbox.

If you only want to have the countries when you have 2 entered in B1, enter following formula in A1 etc.
=IF($B$1=1,C1,IF($B$1=2,D1,"")). The listbox will now be empty if you enter a value other than 1 or 2 in cell B1.

 

[valintine]

Thank you again!

Very useful!
Cheers