Cover a formula in drop down list with a word

Question

I have a list in Sheet2 J3:J7 of calculated values.

I would like to insert it as a drop down list into Sheet1 B10, which is simple enough, however:

I want the list to use the words: apple (J3), banana (J4), grapes (J5), orange (J6), pineapple (J7) rather than the calculated values, yet the cell B10 goes onto be used in other calculations, so although the fruit name will appear - its associated formula needs to, somehow, be in there too, in order to be used in the calculations...

Make sense? Any Ideas?

Answer 1

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.

Answer 2

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.