Search code examples
excelmodelingvba

Changing cells dynamically from the results of a drop down list in excel

I was building a financial model and ran into this problem. Cell C2 contains the dropdown list and the value E25 changes according to the dropdown list. What I want is for the present value column in K22:K25 to change automatically. FOr example, if I choose normal in the dropdown list, I want the value of E25 to update automatically in K22. Thanks in advance! This is the excel file

Solution

  • You might use a formula like this in K22:K24. 

    =Index($J$22:$J$24, MATCH($C$2, $I$22:$I$24, 0), 1)

    This will give you one of the values in J22:J24, depending upon what was chosen in C2. I presume you would want to multiply that value with something. That might be a value you extract from another range using a similar function.

    J22:J24 must contain the same values as the dropdown. Normally, you would do all of this with named ranges and use the same named range to feed the dropdown as you use for determining the choice the user made.

    Try the MATCH function independently to get a better feel for it.

    = MATCH($C$2, $I$22:$I$24, 0)

    It will return a number between 1 and 3, depending on the choice in A3.