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
InsertNameDefine
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
DataValidation
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


" wrote:

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?

See below ... and Thank you

Ron Coderre wrote:
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
InsertNameDefine
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
DataValidation
Allow: List
Source: =OFFSET(LU_ProdCost,0,0,,1)
Click the [OK] button

*** 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


" wrote:

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?

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

See below ... and Thank you

Ron Coderre wrote:
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
InsertNameDefine
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
DataValidation
Allow: List
Source: =OFFSET(LU_ProdCost,0,0,,1)
Click the [OK] button

*** 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


" wrote:

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?

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

Thanks!

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

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


"marley" wrote:

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

Thanks!