He asked me if it was possible to have a drop-down list that shows numbers based on the two specified numbers. Something as shown below:
Note that the drop-down start from the number in column A and goes up to the number in Column B. For example, the drop down in D2 shows numbers from 1 to 10, and the one in D3 shows numbers from 5 to 20, and so on. This kind of drop-down can be created using a helper column and INDIRECT function. Let’s dive in and see how to create this.
Using INDIRECT Formula
This idea in this method is to use the INDIRECT function to create a range that would show numbers between the two specified numbers. To do this, I have used a helper column. Here are the steps to use the INDIRECT formula to create the drop-down between specified numbers:
In column C, enter the numbers from 1 to 1000 (you can do this quickly using the fill handle). The idea is to cover all the numbers that can be used in the drop down. This will be our helper column. Select the cell or range of cells in which you want the drop-down. Go to the Data tab and click on Data Validation. In the Data Validation dialog box, within the settings tab, select List from the drop down. In the Source field, enter the following formula: =INDIRECT(“$C$”&A2+1&”:$C$”&B2+1) Click OK.
That’s it! It will create a drop-down list that will show numbers that are in between the two specified numbers. How does this work? The helper column’s role is to provide a range of cells that can be referred to in the drop-down formula. The INDIRECT formula creates this range by using the numbers in columns A and B. Note that in the formula, I have added 1 to the number (A2+1 and B2+1), as the helper column numbers start from the second row. Click here to Download the Example file.
Select Multiple Items from a Drop Down List in Excel. How to Create a Dependent Drop Down List in Excel. Extract Data from Drop Down List Selection in Excel. Show Symbols in Drop-Down Lists in Excel How to Make a Yes/No Drop-Down in Excel? How to Remove Drop-Down List in Excel?
