I've got a set of values (upper and lower limits of ranges) which I use in conditional formatting. This set of values should change depending on the input in the user form. At the moment there are 3 options but it might be more. So I have created 3 scenarios for these options. Now I need to change scenarios depending on the input. I am thinking something like this:
The set of values looks like on the attached image.
I am not sure if scenarios is the best way to change the values in the set. In the previous version I had separate models for Dataset1 and Dataset2 and only Dataset2 has two options for these boundaries (Tier 1 and Tier 2), so I had an imbedded radio buttons linked to a cell on the sheet with boundaries. So for Tier 1 it shows 1 and for Tier 2 it shows 2. And then for each number I had an IF formula to change the boundaries. But with three options and potentially more, the IF formula becomes quite cumbersome.
VBA Code:
If UserForm1.caftype.Value = "Dataset1" Then
Show Scenario 1
Else
If UserForm1.radiotier1.Value = True Then
Show Scenario 2
Else
Show Scenario 3
End If
End If
The set of values looks like on the attached image.
I am not sure if scenarios is the best way to change the values in the set. In the previous version I had separate models for Dataset1 and Dataset2 and only Dataset2 has two options for these boundaries (Tier 1 and Tier 2), so I had an imbedded radio buttons linked to a cell on the sheet with boundaries. So for Tier 1 it shows 1 and for Tier 2 it shows 2. And then for each number I had an IF formula to change the boundaries. But with three options and potentially more, the IF formula becomes quite cumbersome.