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:
- Drag an Input Text object from the Text section of the Components window onto the canvas.
- In the properties window of the Input Text component, bind the Link to Cell as well as Destination to cell D3 from the Getting ready section.
- Go to the Behavior icon of the input text properties and make sure Treat All Input As Text is unchecked.
- The blue cell D6 from the Getting ready section that's labeled as valid value will check to make sure the input text entered by the user is valid. To do this, we use the following formula:
=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".
- Create the pop up by dragging a Label text component onto the canvas with Input is invalid as its text. Next, go to the behavior tab and for dynamic visibility, bind it to D6 and set the Key as 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.