Automatically populate value of cell dependent on adajecent cell

[Crystal84]

I'm not sure if I need a formula or a script for this, I've looked a
almost every formula in excel and I can't figure it out! Here's wha
I'm trying to do:
In cells E3:E29 I have a drop down menu with a list of services.
Depending on what the selection is in those cells, I would like the nex
cells F3:F29 to automatically populate with a value. For example in E
"Lesson Mozart" would be selected, so F3 would automatically hav
$70.00. Then E4 "Training ride" would be selected so F4 woul
automatically have $40.00. In addition, I would like the G colum
cells to have a value dependent upon the E or F values, but I image whe
I figure out the first part of the problem, I get the next part! Than
you again

 

[lhkittle]

I'm not sure if I need a formula or a script for this, I've looked at

almost every formula in excel and I can't figure it out! Here's what

I'm trying to do:

In cells E3:E29 I have a drop down menu with a list of services.

Depending on what the selection is in those cells, I would like the next

cells F3:F29 to automatically populate with a value. For example in E3

"Lesson Mozart" would be selected, so F3 would automatically have

$70.00. Then E4 "Training ride" would be selected so F4 would

automatically have $40.00. In addition, I would like the G column

cells to have a value dependent upon the E or F values, but I image when

I figure out the first part of the problem, I get the next part! Thank

you again!

In column J is a list of all the services that are available from the drop downs on column E. (Say the list goes from J1 to J45.
In column K is the price for that service.

In F3 enter =VLOOKUP(F3,$J$1:$K$45,2,0) and pull down to F29.

Do a similar vlookup for G column.

Regards,
Howard

 

[CellShocked]

I'm not sure if I need a formula or a script for this, I've looked at
almost every formula in excel and I can't figure it out! Here's what
I'm trying to do:
In cells E3:E29 I have a drop down menu with a list of services.
Depending on what the selection is in those cells, I would like the next
cells F3:F29 to automatically populate with a value. For example in E3
"Lesson Mozart" would be selected, so F3 would automatically have
$70.00. Then E4 "Training ride" would be selected so F4 would
automatically have $40.00. In addition, I would like the G column
cells to have a value dependent upon the E or F values, but I image when
I figure out the first part of the problem, I get the next part! Thank
you again!

I use tables and vlookup to do this.

So make a series that has your sales items in the first column and their
respective prices in the second. Highlight the entire table, and up in
the upper left of your spreadsheet window, where the cell address is
shown, you enter a name for your "table", which in excel is referred to
as a "range" of cells, and the name you give it is called a "range
name".This will make future formulas which reference this table easier to
write.

Then, in your "F" column, you would enter a vlookup formula, which
refers to that first column in your named range, and then looks up the
column number you tell it and returns that intersecting cell value.

So, your vlookup for cell F3 would refer to the value you selected in
E3 and lookup the particular column you declare in your table, so you can
look up more than one related value this way.

so try this is F4 AFTER you name your table (range). You can change
this range name in the future (or now) but it and your formula must
match. I use the name "PriceTable". You should change that name either
now or after the exercise (easily done).


in F3

VLOOKUP(E3,PriceTable,2,FALSE)


That looks at the value you entered (or selected) in E3, and looks up
that value in a table named "PriceTable", and returns the value in the
number 2 column (from left to right)within the table.

or see;

http://www.mediafire.com/view/?d7drt2pn3008758

 

[Crystal84]

Thanks so much! That was easy, but I never would have figured tha
out!


I use tables and vlookup to do this.

So make a series that has your sales items in the first column an
their
respective prices in the second. Highlight the entire table, and up in
the upper left of your spreadsheet window, where the cell address is
shown, you enter a name for your "table", which in excel is referred to
as a "range" of cells, and the name you give it is called a "range
name".This will make future formulas which reference this table easie
to
write.

Then, in your "F" column, you would enter a vlookup formula, which
refers to that first column in your named range, and then looks up the
column number you tell it and returns that intersecting cell value.

So, your vlookup for cell F3 would refer to the value you selected in
E3 and lookup the particular column you declare in your table, so yo
can
look up more than one related value this way.

so try this is F4 AFTER you name your table (range). You can change
this range name in the future (or now) but it and your formula must
match. I use the name "PriceTable". You should change that nam
either
now or after the exercise (easily done).


in F3

VLOOKUP(E3,PriceTable,2,FALSE)


That looks at the value you entered (or selected) in E3, and looks up
that value in a table named "PriceTable", and returns the value in the
number 2 column (from left to right)within the table.

or see;

http://www.mediafire.com/view/?d7drt2pn300875