You can keep a table on another sheet which has the price for each item. Then, you can have a VLOOKUP formula which returns the price depending on which item was picked from your data validation dropdown list.
So, I've attached an example. On Sheet1 there's a dropdown list in C2. Pick an item and the corresponding price is returned in cell C3. The formula in C3 is this:
So, this formula says, "If C2 is blank then return a blank, otherwise look up the exact C2 value in the first column of Table1 and return the corresponding value from the second column of Table1".
Table1 is on Sheet2.
The reason I used a table is because you can add more items to it and the data validation dropdown list in C2 will automatically expand with it. Tables were introduced into Excel in version 2007 so you might not be able to use them (depending on which Mac version you have). If you have an earlier version then let me know and I'll give you a different example.
Bill Jelen has just coordinated a "VLOOKUP week" and collated together lots of articles and videos about VLOOKUP. If you're interested in learning about it then check out the VLOOKUP week blog. You might find Mike Girvin's videos easy to follow (start with video #1).
Thank you so much for doing this Colin, I'm afraid that my version of Excel for mac will not let me open it. It defaults to the Numbers application. Even rich clicking, will not let me choose Excel. Are you able to save it in another format? The file extension I get is XLS