1

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?

4

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.

  • Thank you for this - sorry though i'd seen a lot of posts about excel – Emily G Jun 29 '17 at 11:56
  • My issue is the value to look up is constantly changing via other formulas – Emily G Jun 29 '17 at 12:05
  • @EmilyG you need to create a matching table and then use VLOOKUP – Jonathan Jun 29 '17 at 12:19
  • @Max: yes, SuperUser seems to be the place for questions about Microsoft Excel – danicotra Jun 29 '17 at 17:44
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.