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

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


"[email protected]" 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
> 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
>
*** 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
>
>
> "[email protected]" 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
> > 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
> >
> *** 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
> >
> >
> > "[email protected]" 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!
>
>