Control content of cells with dropdown menu

Hello there! I want to be able to fill in cells B2:B26 with a certain value based on the selection of a dropdown (Combo Box) menu in cell B1. How can I achieve this? For example:

Dropdown menu in cell B1 has options:

  • Low - Fills B2:B26 with value 0
  • Medium - Fills B2:B26 with value 50
  • High - Fills B2:B26 with value 100

Thanks in advance!

First, create a dropdown list in cell B1. It’s simple, select Data - Validity - Criteria.


Now fill in the entire range B2: B26 with the formula


(If the formula does not work, see what settings are set for Separators)

Hey thanks a lot, this actually works great. My problem now is that I want actually different values than 0,50,100. It was just a generic example. And don’t know now how to set those values in the formula. Also is it possible also to use negative values? (The values also may need to be changed by hand later so the list can update) For example I may set High to 247 today, but tomorrow I might need to set it as 500)

Again many thanks for the help.


It was just a joke. I realized that you, like most of the questioners, deliberately simplified the problem statement. And so I gave a solution suitable for this very simplified task and only for it. I hope you are not offended. In fact, the formula for cell B2 can be like =IFERROR(LOOKUP($B$1;{"High";"Low";"Medium"};{247;0;50});"") (Note! The searched values are listed in alphabetical order!). The formula for cells B3:B26 is much simpler =IF($B$2="";"";$B$2) Thus, you change the value only in the formula of cell B2 and immediately get the desired result.

No offense taken! :slight_smile: The one with the knowledge is also the one with the jokes. I am just noob in this and just trying to solve one time problem :slight_smile: Thank’s a lot for the help, it works great now!