Drop down box of names with fixed values?
 

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

Drop down box of names with fixed values?
 

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

InsertNameDefine
Names in workbook: LU_Fabric
Refers to: (your selected list)
Click [OK]

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

Now switch to Sheet1

A1: Fabric
Select A2
DataData 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" wrote:

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

Drop down box of names with fixed values?
 

William

Assume you have two columns on Sheet2

A with fabrics to A40

Give A1:A40 a name through InsertNameDefine. I'll call it fablist

B with Cost for each to B40

Switch to Sheet1 and in A1 Add a DataValidationList 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

On Fri, 23 Dec 2005 15:32:02 -0800, "Soccer Guy" <Soccer Guy
@discussions.microsoft.com wrote:

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

Drop down box of names with fixed values?
 

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" wrote:

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

InsertNameDefine
Names in workbook: LU_Fabric
Refers to: (your selected list)
Click [OK]

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

Now switch to Sheet1

A1: Fabric
Select A2
DataData 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" wrote:

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

Drop down box of names with fixed values?
 

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

William

Drop down box of names with fixed values?
 

Thank you.