Drop Down List with formulas and Vlookup (maybe)

I've been using excel for a while just to do charts with no formulas. Now I'm just beginning to realize there is more to Excel, so far I've gotten use to simple functions.

This little chart I'm trying to make is a little challenging to execute the right format. So her goes:

Basically I would like to create a chart for the sales associates to let them know how much money they have to make a week to achieve the million dollar goals and above.

Column A: "Associate" would have the Associates Name
Column B: "Sales" would have the amount of sales they have achieved.
Column C: "Goal" I have the Goal drop down list $1 Million, $1.5 Million, $2 Million, $2.5 Million,... up to $5 Million
Column D: "Sales Remaining" would have the remaining money needed to hit the value amount in Column C. So Column C#-Column B# of that Row#
Column E: "Sales per Week" would take the amount in Column D and divide it amongst the weeks remaining. To give a weekly goal.

I've gotten Column E formula to work {its the simplest one}, but my problem when selecting the drop down list it would summon the value, and input it in the formula for Column D.

Example John Doe has $543,343 sales in, he is trending to be goaled at $1 Million. He has $456,657 Remaining. Since there is 20 weeks left he has to do $22,833 a week.

I know I could remove the drop down list and just have people enter the numeric amount and just have column C#-B# but I know I'm going to run into this function and formula a little more often. In addition, this was the format I had visioned and would like to execute it.

The weeks remaining is already declared about the table and that function is already taken care of...

I would greatly appreciate any help/feedback.

Thanks

Shanna


MillionDollarTracker.xlsx

Found the solution