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!

On Friday, May 17, 2013 7:46:32 PM UTC-7, Crystal84 wrote:
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!









--

Crystal84

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

On Sat, 18 May 2013 03:46:32 +0100, Crystal84
wrote:


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

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


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[/quote]