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

  • Thread starter Thread starter missmarley
  • Start date Start date
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?
 
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
 
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.
 
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...
 
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
 
Back
Top