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!

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


"Abi" wrote in message
...
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!

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

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 he

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

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

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

Under Data Validation, look for 'Order Form'


Abi wrote:
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!


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html