The ability to input values into the dashboard is a very useful feature. In the following example, we have a sales forecast that changes according to an inputted number value. If we were to use a slider component for the input value, it would be more difficult for the user to select their desired input value. Another good example could be a search box to find a value on a selector which has over 100 items. This way you don't need to hunt for your value. Instead, you can just type it in.
In this recipe, we will create an input textbox to control a what-if scenario.
Create a chart with its values bound to cells that will be controlled by the input textbox value. The following is an example of a sales forecast chart and its cells that are controlled by the what-if scenario:
=IF(ISNUMBER(D3),IF(AND(D3>=-20,D3<=20),D3,"INVALID"),"INVALID")
The formula checks to make sure that the cell contains a number that is between -20 and 20.
Now every cell in the chart binding destination will depend on D6. The binding destination cells will not add the D6 value if D6 is "INVALID". In addition, a pop up will appear saying "Input is invalid" if D6 is "INVALID".
In this example, we use an input value textbox to control the forecast bars on the chart. If we type 20
, it will add 20 to each value in the forecast. If we type -20
, it will subtract 20 from each value in the forecast.
We also add a check in step 4 that determines whether the value entered is valid or not; hence the use of Excel formulas. If a value is invalid, we want to output an error to the user so that they are aware that they entered an invalid value.
For more information on dynamic visibility, please read Chapter 4, Dynamic Visibility.