I'm not sure SuperUser is the place for questions about Microsoft Excel, but while we're here...

I think this is a job for VLOOKUP.

If your Sheet2 looks like this:

Sheet2

then you have your drop down only reference the Names list, but anything that uses the calculated values would use a VLOOKUP:

=VLOOKUP([dropdown cell],Sheet2!$I$3:$J$7,2)

Make sure you have $s in the table reference part of VLOOKUP.

Sheet2!***$I$3:$J$7***

Here's what Sheet1 might look like, with the drop down, then another cell with the calculated value that corresponds to the selected name from the drop down:

Sheet1

Full explanation of VLOOKUP from Office: Excel / Formulas and functions / Reference / VLOOKUP function.


I think Max has offered fine solution. The key is that once the user has selected a value to fill B10, say banana, another cell, say C10, can contain a VLOOKUP() to retrieve the result of a banana-specific formula.