Data Validation List with variable

How to make a drop down list using numbers from 1 to x. x is a variable that can be set.
WTC_ServicesAsked:
Who is Participating?
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
See attached with Dynamic List for Data Validation dropdown.

I have created a list in column J based on Maximum in K1. This increments by 1 if less than or equal to maximum otherwise zero. This only goes to row 40 but you could extend further if you wished by copying the formula further down.

I then a Dynamic named range on this list using the Offset function:

=OFFSET(Sheet1!$J$1,0,0,COUNTIF(Sheet1!$J:$J,"<="&Sheet1!$K$1),1)

If you need the Offset function explaining, let me know.

This named range is called NUMBER_LIST which is then the source list for the Data Validation in F3

Hope that helps.

Thanks
Rob H
Dynamic-Dropdown.xlsx
0
 
Rob HensonFinance AnalystCommented:
Slight confusion in the question. Do you want a dropdown or do you want Data Validation?

There is an option in Data Validation for Whole Numbers whereby you specify the Minimum and maximum values. Each of these can be linked to a cell so you can set the Minimum to 1 if so required and link the maximum to a cell to give the variable maximum.

When using Data Validation for a range of numbers it doesn't give the option for a drop down list like text entries do.

Alternatively, you could create a list starting at 1 and using an IF statement to increment by 1 if smaller than Maximum and then create a Dynamic named range on that list to use as a list option for the DV.

Thanks
Rob H
0
All Courses

From novice to tech pro — start learning today.