2

I'm trying to build a spreadsheet that allows a user to select a product in a dropdown menu in the first cell and then based on what is selected it will populate the spreadsheet with prices and products included. I have zero idea how to go about doing this as I'm incredibly novice at this. Gotta learn somehow though.

  • Why don't you take the tour and get yourself familiar and earn that badge. Welcome on Web Applications !! – Jacob Jan Tuinstra Mar 23 '15 at 18:21
3

See this example spreadsheet. Sheet Countries contains source data (instead of prices and products, I've used countries, population and other data, but the concept is the same). Sheet Dropdown contains the example. In that latter sheet, cell C1 and E1 are dropdown menus (use the Data→Validation… menu to see how data validation works, or look at the explanations at the bottom of this answer).

Then, in cell A2, you have a query that fetches data from the Countries sheet, based on the values present in your dropdown menus. The trick is to build the query string using the string concatenation operator (&). What the query string says is: fetch data from columns A to E of the Countries sheet, where column C is lower than Dropdown!C1 and column E is lower than Dropdown!E1.

Note that you will need to copy the example spreadsheet to play with it (you can only comment the original).

Explanations To set a cell as a drop down menu, select the cell, then use the Data→Validation… menu. For instance, for cell C1 of the Dropdown sheet,list of items you can enter the list of values you want users to select from by setting the Criteria to List of items and then enter comma separated values in the input box right next to it. Other options of this validation menu are self explanatory.

Another common dropdown menu is where you want users to select values from a range within the spreadsheet: list from range As you can see here, the Criteria is now set to List from a range. You enter the range itself by clicking on the grid within the input box, and then by selecting the range of cells you want to use as your selectable items. In the case of our examples, the values come from the sheet Population range where I made some computations to generate a log scale list of values between the smallest country population and the largest.

Hope this helps.

  • Thanks for the post Jean-Rene. OP has zero idea, so explaining how to build a drop-down would be helpful as well. – Jacob Jan Tuinstra Mar 24 '15 at 8:06
  • Drop downs I managed. I figured that out and got stuck. Thanks for the comment! I'll play with this and see what I can accomplish. – Voxil Mar 24 '15 at 18:32
  • Hi Voxil, I added some more explanations. If you find it useful, you can use the arrows next to my answer to state so. – Jean-Rene Bouvier Apr 6 '15 at 9:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.