If then or look up ?

[Kathleen]

Hi,

I have a drop down list containing 12 items in a1. I want b1 to enter the
price based on the item chosen in a1. Is that possible?

a1 = ring
b1 price = $12
a1 = bowl
b1 price = $5
a1 = empty cell
b1 price = $0.00

Kathleen

 

[Laroche J]

Kathleen wrote on 2009-06-15 19:58:

Hi,

I have a drop down list containing 12 items in a1. I want b1 to enter the
price based on the item chosen in a1. Is that possible?

a1 = ring
b1 price = $12
a1 = bowl
b1 price = $5
a1 = empty cell
b1 price = $0.00

Kathleen

First you create a 13-line by 2-column table somewhere. Let's assume for
this example from F1 to G13. In column F you put your item names, and beside
in column G their price. One of the item names must be a single space to get
your empty A1 and a corresponding price of 0.

There are two ways to create a drop-down: with a Combo Box or with Data
validation. In both instances the source or input range would be F1:G13.

With a Combo Box from the Forms toolbar, you'll be able to define a Cell
link, that will give you the rank of the selection. If the Cell link is E1,
then in B1 you'd then use =INDEX(F1:G13,E1,2)

With Data / Validation (Allow: List), the formula for the price in B1 would
be =VLOOKUP(A1,F1:G13,2,FALSE)

JL
Mac OS X 10.4.11
Office v.X 10.1.9, Office 2008 12.1.latest

 

[Kathleen]

Hi,

Fantastic!! One more question. I put the prices on a separate tab and then
used vlook up so the formula looks like:
=vlookup(u2,prices!a2:b14,2,false)

How can the formula be copied to the end of the column? When I tried to do
it, the formula adjusted the "u2" but the "a2:b14" also changed.

Kathleen

 

[CyberTaz]

Your reference to the the lookup table needs to be absolute:

=vlookup(u2,prices!$a$2:$b$14,2,false)

Note: Just select the reference A12:B14 in your formula, press Control+T to
insert the $s, then go ahead & copy the formula.

 

[Kathleen]

That's just BEAUTIFUL!! Thank you!

Your reference to the the lookup table needs to be absolute:

=vlookup(u2,prices!$a$2:$b$14,2,false)

Note: Just select the reference A12:B14 in your formula, press Control+T to
insert the $s, then go ahead & copy the formula.