excel quickie

[billb]

if i have 2 columns with eg products in one column and their correspoding price in the other how do i create a drop down list where when you pick the product in one cell from the drop down list the corresponding price appears in the cell next to it?

thanks

[TheGroover]

Set up a 3 column table like this :

Index Product Price
1 A 10
2 B 15
3 C 20
4 D 6
5 E 9

(you can hide the index column if needs be)

Create a combo box (on the Forms Toolbar), set the Input range to the product list (A to E here) and the cell link value to a cell of your choice. The cell link will display the index of the currently selected item in the drop down.

Then, in the cell you want the price to appear in, write a formula like :-

=VLOOKUP(cell_link_cell, table_range, 3)

The 3 refers to the 3rd column of your table.



>> Edited by TheGroover on Friday 20th February 16:11

[atom290]

Sorry, but a better way to do it is use the validation under the data drop down on your tool bar.

This allows you to select a list that will appear in the cell.

inserted combos are ok, but a little tempromental!

Any problems send me a mail and I will talk you through it

[billb]

TheGroover said:

Set up a 3 column table like this : Index Product Price 1 A 10 2 B 15 3 C 20 4 D 6 5 E 9 (you can hide the index column if needs be) Create a combo box (on the Forms Toolbar), set the Input range to the product list (A to E here) and the cell link value to a cell of your choice. The cell link will display the index of the currently selected item in the drop down. Then, in the cell you want the price to appear in, write a formula like :- =VLOOKUP(cell_link_cell, table_range, 3) The 3 refers to the 3rd column of your table. >> Edited by TheGroover on Friday 20th February 16:11

works a treat thanks my user thinks i'm a genius now!

[TheGroover]

Glad to be of service!