Cover a formula in drop down list with a word
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:
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:
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.