I'm sure Excel is capable of performing the function I require, but for the life of me I can't figure out how to do it. The "IF" formula isn't practical because there are too many prices and variables involved. The VLOOKUP formula seems to be what I need (but I'm not exactly sure) but I've tried and tried and I can't get the return I need.
In a nutshell, here is what I want to be able to do.
On Worksheet Sheet1
I have used the Data Validation method to create a drop down list of 10 Widgets that we sell regularly.
The drop down list was created using a Name Range of "Widget" - The name range itself is located on Sheet2.
On Worksheet Sheet2
I have a list of 10 Widgets that we sell at a fixed price,
The Text description of these 10 Widgets are listed in Column A A2:A11
The corresponding selling price for each of these 10 Widgets is listed right next to it in Column B B2:B11
When I use the drop down menu on Sheet1 to select one of the 10 items, I want the corresponding sale price to automatically appear in the cell to the immediate right of item selected in the drop down list. Obviously the value returned is dynamic based upon the item selected.
Can anyone explain to me how this is done, what formula to use and how is should be configured?
Thanks so much.
Bookmarks