Imagine a scenario where you want your users need to select from a list of thousands of products. The easiest way to handle this type of a list would be to let the users first select from a broader category, like the brand, then narrow the results down by letting them select from a quantitative property, and then finally the product itself.
Multiple choice inputs, like dropdowns, work best when you want to give your users a list to choose from. However, a data set with multiple properties for each item will not work in a dropdown-style input field, because each property will be static for the inputs. This means that you might select from a brand, but you will not see the actual products that belong to that brand.
You can create a dynamic list to overcome this limitation. A dynamic list is a list of options that change based on other parameters. For instance, you might have a rather large list of products that are categorized in various groups.
Let’s see how you can build dynamic dropdown lists in designer applications on an example. Here, we have a long list of liquor products that come in different brands, sizes, and price. We’re going to create a pricing tool that allows users to select from each property to find the product they want.
Below is a workbook containing product information. Under the List worksheet, we have the full list of available products with their descriptions, brands, sizes, and price data. We concatenated the information for each product under the Distinct sheet using INDEX and MATCH functions to create unique names. The Quote sheet consists of the input dropdowns that allow selecting from the three properties, as well as the pricing information for that specific selection. For more details about creating these formulas and helper columns please see: https://www.spreadsheetweb.com/pricing-list-quoting-tool-part-1/
Note that dropdown lists created in Excel with this approach will display the blank cells in Excel, but SpreadsheetWEB ignores these, resulting in a clean look on the web.
We begin by creating a new designer application and uploading our file to SpreadsheetWEB. We must then go to the User Interface Designer and start building our page.
We can now create the Description, Brand and Size inputs. Let’s begin by with adding a new section.
Now, we’re going to add the Description field and create a dropdown for this input. Dragging and dropping a dropdown module to the section we just created will add the new field. To customize this field and tie it to the named range, click the module and select the corresponding named range. In this example, we’re using the named range Description for the input, and DescriptionList for the list of options. Enabling the Trigger Calculations option will run the calculations each time this field is changed, thus updating all lists.
We’re also going to repeat the same steps for the Brand input to add it as a dropdown field. The Size field contains fewer options than the other fields, and can work well as a radio button input. For this, we’re going to add a radio button module, and then customize it by clicking on it in the page.
Finally, we add a content field to display the price results. The application is now ready and users can select from Description, Brand, and Size options to display the price of the selected item.
