When I select from drop down menu I would like the next column to populate a specific cost.

M

missmarley

I am creating a form. I have a drop down menu for "equipment" and if I
select a specific piece of equipment from the drop down menu I would
like for the next column to populate a specific cost. How do I do this?
 
R

Ron Coderre

Try this:

Put a new worksheet in your workbook, then:
A1: Product
B1: Cost
A2: Prod_A (or whatever products you have)
B2: Prod_A_Cost
Continue filling in the list

When done...
Select from A2 through the last item in Col_B
Insert>Name>Define
Name in workbook: LU_ProdCost
Refers to: (your already selected list)
Click the [OK] button

Next, switch to your input sheet
Select the Product input cells
Data>Validation
Allow: List
Source: =OFFSET(LU_ProdCost,0,0,,1)
Click the [OK] button

Select the Product cells (I'll assume they begin in cell B2)
B2: =IF(ISBLANK(A2),"",VLOOKUP(A2,LU_ProdCost,2,0))
Copy that formula down as far as you need.

Results:
The Product input cells will only allow valid products.
The Cost cells will lookup each product in the LU_ProdCost list
and return the corresponding Cost.

Is that something you can work with?

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

XL2002, WinXP-Pro
 
M

marley

See below ... and Thank you

Ron said:
Try this:

Put a new worksheet in your workbook, then:
A1: Product
B1: Cost
A2: Prod_A (or whatever products you have)
B2: Prod_A_Cost
Continue filling in the list

When done...
Select from A2 through the last item in Col_B
Insert>Name>Define
Name in workbook: LU_ProdCost
Refers to: (your already selected list)
Click the [OK] button

Next, switch to your input sheet
Select the Product input cells
Data>Validation
Allow: List
Source: =OFFSET(LU_ProdCost,0,0,,1)
Click the [OK] button
Click to expand...
*** I selected the product cells from the LU_ProdCost - is that
correct? I also tried for Product cost, but it did not work for me.
Everything else above came out well.
 
R

Ron Coderre

The Product will be selected from the dropdown list...

The cost will calculate automatically using the formula referenced in my
previous post:
For a Product selected in Cell A2.....
B2: =IF(ISBLANK(A2),"",VLOOKUP(A2,LU_ProdCost,2,0))
Copy that formula down as far as you need.


Does that help?

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

XL2002, WinXP-Pro


marley said:
See below ... and Thank you

Ron said:
Try this:

Put a new worksheet in your workbook, then:
A1: Product
B1: Cost
A2: Prod_A (or whatever products you have)
B2: Prod_A_Cost
Continue filling in the list

When done...
Select from A2 through the last item in Col_B
Insert>Name>Define
Name in workbook: LU_ProdCost
Refers to: (your already selected list)
Click the [OK] button

Next, switch to your input sheet
Select the Product input cells
Data>Validation
Allow: List
Source: =OFFSET(LU_ProdCost,0,0,,1)
Click the [OK] button
Click to expand...
*** I selected the product cells from the LU_ProdCost - is that
correct? I also tried for Product cost, but it did not work for me.
Everything else above came out well.
*** Select the Product cells (I'll assume they begin in cell B2)
B2: =IF(ISBLANK(A2),"",VLOOKUP(A2,LU_ProdCost,2,0))
Copy that formula down as far as you need.

Results:
The Product input cells will only allow valid products.
The Cost cells will lookup each product in the LU_ProdCost list
and return the corresponding Cost.

Is that something you can work with?

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

XL2002, WinXP-Pro
Click to expand...
Click to expand...
 
M

marley

Where does the formula =IF(ISBLANK(A2),"",VLOOKUP(A2,LU_ProdCost,2,0))
go?

Thanks!
 
R

Ron Coderre

Where does the formula =IF(ISBLANK(A2),"",VLOOKUP(A2,LU_ProdCost,2,0))
go?
Click to expand...

The formula goes in cell B2. It references A2 so the cost value for A2 will
display in right next to it in B2.

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

XL2002, WinXP-Pro
 
Top