Dropdown Selections in Excel - creating and choosing

[Abi]

I'm working with Excel 97, but will be getting upgraded to the newest version
in a few days.

I would like to be able to create a two column list on one sheet - one
column has products and the other has prices. Then, on another sheet I would
like a user to be able to use a dropdown, or a form, to be able to select one
or more of these products (along with their adjoining price) to add to a new
sheet, or to remove as necessary.

I've seen something like this done before, but I'm not sure how to do it. I
can't use Access (which would be ideal) because no one else has it, and I
need multiple people to use this.

any help or suggestion are greatly appreciated!

 

[Arvi Laanemets]

Hi

An example

Create a sheet Products, p.e.
Product Price
product1 10.49
product2 6.05
product3 22.00
etc.
Create named ranges (Insert.Name.Define from menu)
Products=OFFSET(Products!$A$2,,,COUNTIF(Products!$A:$A,"<>")-1,1)
ProductTbl=OFFSET(Products!$A$2,,,COUNTIF(Products!$A:$A,"<>")-1,2)

On another sheet, select a range of cells in Product column, and then from
Data menu Validation. Select List from dropdown and enter into source field
the formula
=Products
OK
Now when you select any cell from this range, a dropdown activates for this
cell, and you can select any product from list on Products sheet.
Let the range of cells, formatted in such a way, to be A2:A100. when you
want the price to appear in adjacent cell in column B, whenever a product is
selected, enter into cell B2 the formula
=IF(ISERROR(VLOOKUP(A2,ProductTbl,2,0)),"",VLOOKUP(A2,ProductTbl,2,0))
and copy it to range B2:B100.

It's all.

Arvi Laanemets

 

[Debra Dalgleish]

You can use Data Validation to create dropdown lists. There are
instructions in Excel's help, and here:

http://www.contextures.com/xlDataVal01.html

To return the price for the selected item, you can use a VLookup
formula. There are instructions and examples here:

http://www.contextures.com/xlFunctions02.html

There's a sample file that uses Data Validation and VLookup formulas here:

http://www.contextures.com/excelfiles.html#Function

Under Data Validation, look for 'Order Form'