Drop down box of names with fixed values?

S

Soccer Guy

I'm trying to streamline how to determine costs to make a garment easily. We
have muliple different fabric options, each with its own cost.

How do I create a drop down box that lists the fabric names and also links
this selection to the cost of the fabric so I can easily see the differences
in cost when selecting a different fabric?

Any help would be greatly appreciated.

William
 
R

Ron Coderre

I think you could use a combination of Data Validation and VLOOKUP functions

Try building this model so you can get familiar with the concepts.....

On Sheet2, create a price list

A1: Fabric
A2: Cotton
A3: Rayon
etc

B1: Price
B2: 6.55
B3: 3.20
etc

Select A2 through the last item listed in Col_A

Insert>Name>Define
Names in workbook: LU_Fabric
Refers to: (your selected list)
Click [OK]

Select A2 through the last item listed in Col_A
Insert>Name>Define
Names in workbook: LU_Price
Refers to: (your selected list)
Click [OK]

Now switch to Sheet1

A1: Fabric
Select A2
Data>Data Validation
Allow: List
Source: (click in here, Press the [F3] key, select LU_Fabric)
Click [OK]

Copy A2 down as far as you need inputs

B1: Price
B2: =VLOOKUP(A2,LU_Price,2,0)
Copy B2 down as far as you need.

Of course adjust range references to suit your situation.
Change the fabrics and prices, too....I just made them up :)

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro
 
G

Gord Dibben

William

Assume you have two columns on Sheet2

A with fabrics to A40

Give A1:A40 a name through Insert>Name>Define. I'll call it fablist

B with Cost for each to B40

Switch to Sheet1 and in A1 Add a Data>Validation>List drop down.

The source for this list is the list on Sheet2 and you enter it like this in
the DV source dialog.

=fablist

In B1 enter =VLOOKUP(A1,Sheet2!$A$1:$B$40,2,FALSE)

Select a fabric in A1 and cost will pop up in B1


Gord Dibben Excel MVP
 
R

Ron Coderre

sSlight correction:

The range named LU_Price should refer to Sheet2, cells A2 through the last
item in Col_B.

So if you have 4 items and prices....the LU_Price will refer to Sheet2!A2:B5.

(My apologies for the typo.)

***********
Regards,
Ron

XL2002, WinXP-Pro


Ron Coderre said:
I think you could use a combination of Data Validation and VLOOKUP functions

Try building this model so you can get familiar with the concepts.....

On Sheet2, create a price list

A1: Fabric
A2: Cotton
A3: Rayon
etc

B1: Price
B2: 6.55
B3: 3.20
etc

Select A2 through the last item listed in Col_A

Insert>Name>Define
Names in workbook: LU_Fabric
Refers to: (your selected list)
Click [OK]

Select A2 through the last item listed in Col_A
Insert>Name>Define
Names in workbook: LU_Price
Refers to: (your selected list)
Click [OK]

Now switch to Sheet1

A1: Fabric
Select A2
Data>Data Validation
Allow: List
Source: (click in here, Press the [F3] key, select LU_Fabric)
Click [OK]

Copy A2 down as far as you need inputs

B1: Price
B2: =VLOOKUP(A2,LU_Price,2,0)
Copy B2 down as far as you need.

Of course adjust range references to suit your situation.
Change the fabrics and prices, too....I just made them up :)

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


Soccer Guy said:
I'm trying to streamline how to determine costs to make a garment easily. We
have muliple different fabric options, each with its own cost.

How do I create a drop down box that lists the fabric names and also links
this selection to the cost of the fabric so I can easily see the differences
in cost when selecting a different fabric?

Any help would be greatly appreciated.

William
Click to expand...
Click to expand...
 
S

Soccer Guy

Thank you very much for your help and assistance. Excellent advice and tips.
It solved what I was looking for.

William
 
Top