Populating data from a list

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Ok I'm trying to set up a Sales register for some products I sell in my spare time. I would like to use the list command to create a drop down selector with my products and have it auto fill other cells of my spread sheet

ie:

in drop down menu
Product A
Product B
Product C


If I select Product B

I would need it to fill in on the adj cell the wholesale cost of product b and in the next cell the description of product b

is this even possible or am I asking for a miracle to keep me from having to type the same stuff over and over and over
 
Hi
if you have a list with your product names and the associated costs or
other information you can use VLOOKUP and data validation to achieve
your task:
1. Create a list with your product data. It should look like the
following:
A B C ...
1 ProA 20.00 other data
2 ProB 30.00 other data
3 ProC 40.00 other data
...

In your entry sheet you may first create a list using data validation
(lets say in cell F1)
- select cell F1
- goto 'Data - Validation'
- choose 'List' and select column A as datasource
have a look at the following sites for further info about data
validation:
http://www.contextures.com/xlDataVal01.html
and
http://www.contextures.com/xlDataVal05.html

To get other data based on the entry in F1 you may use VLOOKUP: e.g.
enter the following in G1:
=VLOOKUP(F1,$A$1:$C$10,2,0)
to get the associated cost for a product
for more information about VLOOKUP have a look at Excel's help or
http://www.mvps.org/dmcritchie/excel/vlookup.htm
 
Justin

Set up a list of your products and use a Lookup Formula

e.g
Product A Widget 1 23.45
Product B Widget 2 12.12
Product C Widget 3 45.34

Ref Vlookup1 Vlookup2
Product A Widget 1 23.45

formula 1 =VLOOKUP(G$6,$G$1:$I$3,2,FALSE)
formula 2 =VLOOKUP(G$6,$G$1:$I$3,3,FALSE)

You list has to be in ascending order in first column.
G6 is the lookup reference
G1:I3 is the Data range
2 & 3 or the column offset values.

if you are using Excel 2000 or later then formulas are
copied down automatically when you enter a lookup
reference.

Regards
Peter
Regards
Peter

-----Original Message-----
Ok I'm trying to set up a Sales register for some
Click to expand...
products I sell in my spare time. I would like to use the
list command to create a drop down selector with my
products and have it auto fill other cells of my spread
sheet
ie:

in drop down menu
Product A
Product B
Product C


If I select Product B

I would need it to fill in on the adj cell the wholesale
Click to expand...
cost of product b and in the next cell the description of
product b
is this even possible or am I asking for a miracle to
Click to expand...
keep me from having to type the same stuff over and over
and over
 
Back
Top