Search code examples
excel-2013

Dropdown-List value calculation

Is it possible to create a drop down list with one number as source, and calculate the other values?

Here's what I mean:
Example picture

  • Source is A1.
  • B1 Dropdown-List's start value is 0 and maximum value is A1.
  • Values to A1 (4 in that case) should be calculated with 0 as start value. (1, 2, 3 in that case)
Solution

  • I have tried it in Excel 2010, but I think there will be no significant difference in Excel 2013.

    1. Create a list of all eventually possible values (for example 0-1000). Assume that the values are located at H1:H1001.
    2. Choose a cell in which a threshold will be held. Suppose it will be F3.
    3. Restrict possible values in F3.
      • Select F3.
      • On the Data menu, click Validation.
      • In the Allow box, click List.
      • Uncheck the In-cell drop-down check box.
      • In the Source box, enter =H1:H1001.
    4. Create a drop-down menu for a given cell.
      • Select a given cell.
      • On the Data menu, click Validation.
      • In the Allow box, click List.
      • Check the In-cell drop-down check box.
      • In the Source box, enter =INDIRECT("H1:H"&(F3+1)).
    5. Voilà!

    Here is a working example. Feel free to download it.

    INDIRECT function accepts a string reference to a range and returns the range itself. So if value stored in F3 were 27, the function, as used here, would return a range from H1 to H28 (thus possibilities in the drop-down list would be 0-27).