none
DropDown Formula failling - Excel 2010 RRS feed

  • Question

  • I need to retrieve a dropdown actual value, i.e. the text rather than the index.

    So, based on MS instructions as in https://support.office.com/en-in/article/Add-a-list-box-or-combo-box-to-a-worksheet-5ef0fee8-2a1e-43b2-bfea-67cf48fef130

    Use this number in a formula to return the actual item from the input range.

    For example, a dessert preference form has a list box that is linked to cell C1, the input range for the list is D1:D5, and the items in the range are: "Ice Cream" (D1), "Cake" (D2), "Liqueur" (D3), "Candy" (D4), and "Chocolate" (D5). The following formula, entered in cell B1, returns the value "Liqueur" from range D1:D5 if the value of C1 is 3, based on the current selection in the list box.

    I'm using "INDEX" function.

    It should work, but i get the following message.

    The one difference is that my dropdown Formula links  to another sheet, so my it is like

    DropDown Input range : SomeSheet!$B$3:$B$112
    DropDown Cell link : $D$36

    Retrieval Cell Formula :
    =INDEX(SomeSheet!$B$3:$B$112,D36)

    So, I ask : WHAT IN THE WORLD IS THAT, fellows?

    Thanks in advance!


    • Edited by InfoProfi Friday, September 18, 2015 2:09 AM
    Friday, September 18, 2015 2:07 AM

Answers

  • 1) The formula assumes that you have used a combo box from Forms Controls:

    2) The formula as given is for systems that use comma as list separator and some other character as decimal separator.

    If you use comma as decimal separator, chances are that your list separator is the semi-colon (;).

    In that case, the formula should be

    =INDEX(SomeSheet!$B$3:$B$112;D36)

    3) If the real name of SomeSheet contains spaces or punctuation, you must enclose the name in single straight quotes (aka apostrophes):

    =INDEX('Some Sheet'!$B$3:$B$112,D36)


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, September 18, 2015 8:34 AM