Drop Down Lists To Show Numbers Between Two Specified Numbers

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:

Drop Down Lists with Specified Numbers

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.Drop Down List Between Numbers 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.Data Validation in Ribbon
  • In the Data Validation dialog box, within the settings tab, select List from the drop down.Data Validation List
  • In the Source field, enter the following formula: =INDIRECT(“$C$”&A2+1&”:$C$”&B2+1)Data Validation Formula
  • Click OK.

That’s it!

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:

Excel Ebook Subscribe

FREE EXCEL BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

  • What if column C in a different work sheet, how can I write the formula? Thanks

  • How would you apply this formula if you needed to reference cells in a different worksheet?

  • >
    shares