Drop down list values?

[Wipper2000]

A bit of a stab in the dark really, but I'm trying to do an Excel spreadsheet at work to make costing individual items easier and quicker. I'm getting there slowly as I'm no expert on Excel, but have come to a bit of a standstill and hope someone, somewhere may be able to help with the following:

I have a cell that has a drop down list containing various fabrics we use. I want to put a value to each fabric so that I can provide a different final price depending on which fabric is selected from the drop down box.

Can this be done?

Does anyone actually understand what I'm on about?

Thanks for looking

Gareth

 

[Wipper2000]

What version of Excel are you using?

What is the data type of the items in the drop down list? Is it text, numbers, both?

I'm using Excel 2007 on Windows 7.

The drop down list will be text (product materials).

We manufacture bespoke items from fabrics. Each item we make uses a certain amount of fabric (m2). I have designed the worksheet to calculate the amount of material required based on a few basic size imputs etc. Each material we use has a different cost so the drop down list is supposed to allow me to select a specific material, select it and Excel will automatically adjust the overall cost by multiplying the amount of fabric required x material cost.

Does this make sense?

 

[T. Valko]

I'm using Excel 2007 on Windows 7.

The drop down list will be text (product materials).

We manufacture bespoke items from fabrics. Each item we make uses a certain amount of fabric (m2). I have designed the worksheet to calculate the amount of material required based on a few basic size imputs etc. Each material we use has a different cost so the drop down list is supposed to allow me to select a specific material, select it and Excel will automatically adjust the overall cost by multiplying the amount of fabric required x material cost.

Does this make sense?

Yes it does. Not a problem.

However, I'll be away for a few hours but I'm sure we can get this straightened out for you.

If no one else jumps in I'll be back later this afternoon/evening.

 

[T. Valko]

Ok...

Let's assume on Sheet2 you have a 2 column table of products and prices like this:

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=128 border=0><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>Products</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=64>Price</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>product1</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">10 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>product2</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">15 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>product3</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">17 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>product4</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">100 </TD></TR></TBODY></TABLE>

A1:B1 are the column headers and the data is in A2:B5.

Since this table will be updated with new products from time to time we want to create a dynamic range for this table.

Goto the Formulas tab
Defined Names>Define Name
Name: Products
Refers to: =OFFSET(Sheet2!$A$2:$B$2,,,COUNTA(Sheet2!$A$2:$A$100),2)

Use a reasonable end of range $A$100 that allows enough rows for future additions to the table.

OK out

Now, setup the data validation list.

Navigate to Sheet1
Select cell A1
Data Validation
Allow: List
Source: =INDEX(Products,0,1)
OK out

Now, enter this formula in cell B1 to get the price for the product selected from the drop down list in cell A1:

=IF(A1="","",VLOOKUP(A1,Products,2,0))

As you add new products to the table on Sheet2 they will automatically be added to the drop down list.

Just make sure that when you do add new products to the table, you do it so that there are no empty cells between items in the table. You want the table to comprise a solid block of data.