Drop down box with user defined input

[Rodrigo]

Hi,

I have a drop down box with 4 scenarios that relate to occupancy rates over a fifteen year period. The scenarios are as follows, current, best case, worst case and custom.

The first three are easy enough through a combination of the if and vlookup functions.

How do I go about allowing for the user to input his own values into the occupancy rates if he chooses "custom" from the drop down box.


This is what I have managed for the first three options except for the last option.

=IF(J7=1,VLOOKUP(J17,Occup_LU!H13:I27,2),IF(J7=2,VLOOKUP(J17,Occup_LU!K13:L27,2),IF(J7=3,VLOOKUP(J17,Occup_LU!N13:O27,2),IF J7=4,???????????????????)))


Regards

 

[Hui]

Rodrigo

2 points here

1. You have define 3 separate areas for the Current, Best and worst cases, what other cases have you predefined?

2. Instead of having a separate lookup range ie: H13:I27 and K13:L27 can you rearrange your data to have a common first column? Then you could do something like

=VLOOKUP(B7,Occup_LU!H13:Q27,choose(B8,2,3,4,5)) and simplify and replace the entire formula

Where B8 will give you the chance to pick columns 2,3,4,5 etc from the range H13:Q27

I have used B8 instead of your J7

and B7 instead of your J17 as they need to be out of the data range

 

[Hui]

Rodrigo

If you want to keep the same system of 2 columns of lookup with a blank column between you could try something like:

=VLOOKUP(J17,OFFSET(Occup_LU!E13:F27,0,J7*3),2)

That will allow you to have any number of scenarios of 2 columns with a blank column between them offset to the right

Make sure J7>0 and an Integer.

 

[Rodrigo]

Hi Hui, thanks for the post, the formula simplification works great. I have gone for the option of the common first column.


Now to the part where the user wants to define his own occupancy rates, i.e. the custom scenario. How do i go about allowing the user to define his own occupancy rates for each year of the fifteen year period.

The first three scenarios are predefined, i.e. I have a current scenario with increases/decreases to determine the best and worst case scenarios. The custom scenario is totally up to the user with theoretical inputs from 0% to 100%.

If the user chooses the custom scenario, the year occupancy rate input cell must allow for a % input.


Regards


Rodrigo