software

I am creating a form. I have a drop down menu for quot;equipmentquot; 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
Insertgt;Namegt;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
Datagt;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),quot;quot;,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-Proquot; wrote:

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

See below ... and Thank you

Ron Coderre wrote:
gt; Try this:
gt;
gt; Put a new worksheet in your workbook, then:
gt; A1: Product
gt; B1: Cost
gt; A2: Prod_A (or whatever products you have)
gt; B2: Prod_A_Cost
gt; Continue filling in the list
gt;
gt; When done...
gt; Select from A2 through the last item in Col_B
gt; Insertgt;Namegt;Define
gt; Name in workbook: LU_ProdCost
gt; Refers to: (your already selected list)
gt; Click the [OK] button
gt;
gt; Next, switch to your input sheet
gt; Select the Product input cells
gt; Datagt;Validation
gt; Allow: List
gt; Source: =OFFSET(LU_ProdCost,0,0,,1)
gt; Click the [OK] button
gt;
*** 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.

gt; *** Select the Product cells (I'll assume they begin in cell B2)
gt; B2: =IF(ISBLANK(A2),quot;quot;,VLOOKUP(A2,LU_ProdCost,2,0))
gt; Copy that formula down as far as you need.
gt;
gt; Results:
gt; The Product input cells will only allow valid products.
gt; The Cost cells will lookup each product in the LU_ProdCost list
gt; and return the corresponding Cost.
gt;
gt; Is that something you can work with?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot; wrote:
gt;
gt; gt; I am creating a form. I have a drop down menu for quot;equipmentquot; and if I
gt; gt; select a specific piece of equipment from the drop down menu I would
gt; gt; like for the next column to populate a specific cost. How do I do this?
gt; gt;
gt; gt;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),quot;quot;,VLOOKUP(A2,LU_ProdCost,2,0))
Copy that formula down as far as you need.Does that help?

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

XL2002, WinXP-Proquot;marleyquot; wrote:

gt; See below ... and Thank you
gt;
gt; Ron Coderre wrote:
gt; gt; Try this:
gt; gt;
gt; gt; Put a new worksheet in your workbook, then:
gt; gt; A1: Product
gt; gt; B1: Cost
gt; gt; A2: Prod_A (or whatever products you have)
gt; gt; B2: Prod_A_Cost
gt; gt; Continue filling in the list
gt; gt;
gt; gt; When done...
gt; gt; Select from A2 through the last item in Col_B
gt; gt; Insertgt;Namegt;Define
gt; gt; Name in workbook: LU_ProdCost
gt; gt; Refers to: (your already selected list)
gt; gt; Click the [OK] button
gt; gt;
gt; gt; Next, switch to your input sheet
gt; gt; Select the Product input cells
gt; gt; Datagt;Validation
gt; gt; Allow: List
gt; gt; Source: =OFFSET(LU_ProdCost,0,0,,1)
gt; gt; Click the [OK] button
gt; gt;
gt; *** I selected the product cells from the LU_ProdCost - is that
gt; correct? I also tried for Product cost, but it did not work for me.
gt; Everything else above came out well.
gt;
gt; gt; *** Select the Product cells (I'll assume they begin in cell B2)
gt; gt; B2: =IF(ISBLANK(A2),quot;quot;,VLOOKUP(A2,LU_ProdCost,2,0))
gt; gt; Copy that formula down as far as you need.
gt; gt;
gt; gt; Results:
gt; gt; The Product input cells will only allow valid products.
gt; gt; The Cost cells will lookup each product in the LU_ProdCost list
gt; gt; and return the corresponding Cost.
gt; gt;
gt; gt; Is that something you can work with?
gt; gt;
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP-Pro
gt; gt;
gt; gt;
gt; gt; quot; wrote:
gt; gt;
gt; gt; gt; I am creating a form. I have a drop down menu for quot;equipmentquot; and if I
gt; gt; gt; select a specific piece of equipment from the drop down menu I would
gt; gt; gt; like for the next column to populate a specific cost. How do I do this?
gt; gt; gt;
gt; gt; gt;
gt;
gt;

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

Thanks!gt; Where does the formula =IF(ISBLANK(A2),quot;quot;,VLOOKUP(A2,LU_ProdCost,2,0))
gt; 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-Proquot;marleyquot; wrote:

gt; Where does the formula =IF(ISBLANK(A2),quot;quot;,VLOOKUP(A2,LU_ProdCost,2,0))
gt; go?
gt;
gt; Thanks!
gt;
gt;