Display Countries According to Revenue Input

[Former Member]

Hello Everyone,

We have predefined list of countries and their revenue. I am looking for a functionality where a user gives a random number of his choice in a input box. then the excel has to pick the countries that are less than the revenue given and write them to seperate cells, then a spreadsheet table has been setup to show the resultant countries to the user. Here I have included few screenshots.

Pre-Defined Revenue values:

So according to this, 43270 is revenue given by user and the excel has to write, Germany Spain and Norway to seperate cells.

Any inputs are greatly appreciated. Thanks in advance

[Former Member · Accepted solution]

Hi R V,

Lets presume that in your picture the countries are in column A and the figures in column B.

In column C add a formula which returns 1 if the number in column B is higher than the target (random number entered by user) and 0 if it is not. The formula for USA should be =if(B2< 43270,0,1).

Once the third column contains 0 or 1 depending if the number is lower/higher create a combo box which only picks up the records with a 0. (labels should be 0 and 1, Filtered Rows, Item = 0)

Good luck with your dashboard and if you have additional questions please do not hesitate to contact me,

Regards,

Matt

[Former Member · Accepted solution]

Here's an Excel-based solution.

Result:

Formulas:

[Former Member · Accepted solution]

In addition to my answer:

1. Make sure in the formula you don't hardcode the number. the formula should compare the figure for each individual country with the figure from the destination of the text box.

2. Create a Lower and Higer section (like in my picture) - this will allow you to tell the Combo Box if you want to display the values which are higher or lower than the target (do this in the Behaviour item of the section --> Item --> bind to G2 if you want lower or to H2 if you want higher.

3. All the other settings of the combo box and the formula can be seen in the picture.

4. Good luck!

Regards,

Matt