Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Data Validation List with variable

Posted on 2014-04-29
2
Medium Priority
?
204 Views
Last Modified: 2014-09-17
How to make a drop down list using numbers from 1 to x. x is a variable that can be set.
0
Comment
Question by:WTC_Services
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
2 Comments
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40031772
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
 
LVL 33

Accepted Solution

by:
Rob Henson earned 2000 total points
ID: 40031807
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

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question