Solved

Data Validation List with variable

Posted on 2014-04-29
2
184 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
  • 2
2 Comments
 
LVL 31

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 31

Accepted Solution

by:
Rob Henson earned 500 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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

762 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now