I recently got an email from one of my readers with an interesting query on using drop-down lists in Excel.
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 number. 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.
It will create the 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 cell that can be referred in the drop-down formula.
The INDIRECT formula creates this range by using the numbers in column 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.
You May Also Like the Following Excel Tutorials: