CODEWITHSUNDEEP
exceldrop-down-menu
nbg15
nbg15

Reputation: 129

Excel: dynamically numeric Dropdown

i want to create in excel an dropdown to vote for something like

"how important is the weather for You"

and the user can choose from 1 to 10

BUT

the I want to give the user the possibility to change the range

for example

From 1 - 10 To 1 - 5

therefore I have to fields in my excel sheet

From: To:

and the user can add there some values and exactly these values I want to display then in my dropdown box?

How can I do this?

I have alreay found many solution for standart dropdowns like

Choose your car!

VW BMW Mercedes

and then i can create a dropdown based on a source column, but in this case i want to fill my dropdown with a FROM - TO Range

for any hints I would be really happy!

Thank you very much!

greetz

Upvotes: 0

Views: 98

Answers (1)

Scott Holtzman
Scott Holtzman

Reputation: 27239

Create a Dynamic Named Range.

In this case, I created a column on a sheet with the heading Scale.

  • In the first cell I wrote =From (a named range based on the From cell).

  • In the second cell I wrote the formula =IF(G2="","",IF(G2+1>To,"",G2+1)) where To is a named range based on the To cell. (The column I used was G in my example).

  • I then dragged down the formula for a bunch of rows (however many you need for the range).

  • I then created a Named Range (Scale) with the formula: =OFFSET(Sheet1!G2,0,0,COUNTA(Sheet1!$G:$G)-1,1) (-1 is to exclude header row)

  • Then create a Data Validation on the drop-down cell = to Scale. You will see the values pop up between From and To based on whatever entries are in that cell.

Upvotes: 3

Related Questions