Need to create a project schedule? Try our Gantt Chart Template!

Dynamic Drop-Down Lists for Dates and Check Numbers

Dynamic Drop-down List in Excel

If you use Excel for money management or any type of activity where you are entering daily transactions or logging daily activities, then you may find it useful to create a dynamic drop-down list. This technique came about through the use of the Money Management template, so I will use that template as an example.

In the money management template, the Transactions worksheet provides a table for entering bank and credit card transactions, just like you would find in any check register or personal finance software. When we add new transactions, we want to be able to select the next date and next check number from a drop-down list as shown in the images below.

Dynamic Drop Down List for Dates
Dynamic drop-down list for Dates
Dynamic Drop Down List for Next Check Numbers
Dynamic drop-down list for Check Numbers

The list of dates includes the most recent date plus the dates for the next week. It would be much better if Microsoft provided a convenient built-in date picker tool, but this approach provides a useful substitute for now. We can still enter the date manually if we want to.

As you may know from your own experience, checks that you write are not always deposited in the order you write them. So, for the drop down list in the Num column, we can list the next three check numbers. (Note: after writing this article and creating these images, I found that I also wanted to list the previous two check numbers).

Setting up the Dynamic Drop-Down Lists

Step 1: Create the lists

The lists used for the dynamic drop-downs need a home, so for this example we can create a new worksheet and create the lists as shown in the image below. The formula in A6 is adding +1 to the current maximum check number in the Num column. Cell C2 is finding the most recent date in the Date column. The rest of the items in the list are just calculated from those two cells as shown.

Formulas for the Drop Down Lists
Formulas for the drop down lists

Step 2: Name the lists

The next step is to name the ranges “num_list” and “date_list” by selecting the list (not including the header in row 1) and using the Name Box to name the ranges as shown in the next image.

Creating the Named Ranges
Naming the lists

It isn’t necessary to use named ranges when creating drop-down lists, but if you ever want to redefine the list, it’s easier to redefine the named range than to update all the cells that use data validation to refer to the list. That brings us to the last step.

Step 3: Create the in-cell drop-downs

To set up the data validation drop-down lists, just select all of the cells in the Num column and go to Data > Data Validation, select List from the Allow field, and enter =num_list in the Source field, as shown in the image below. Do likewise for the Date cells to reference the date_list range.

Data Validation Settings
Data Validation Settings
Important: To Avoid the Data Validation Error Message …

It’s important that when you set up the data validation drop down list you uncheck the “Show error alert after invalid data is entered” in the Error Alert tab. This allows you to still enter any check number of date that you want, without Excel popping up an error warning.

That is really all there is to it. If you find other uses for this technique, please let me know.


9 comments… add one
  • Another Tip: One thing that I like about Quicken and dates is that while you have a date selected you can press “+” (or rather “=”) to increase the date, or “-” to decrease the date. So far, I’m finding that the dynamic drop-down list technique above is almost as good (assuming the transactions or the log is almost daily). You can use the keyboard shortcut ALT+DownArrow to activate the dropdown and then press the Down Arrow again to select the next date.

  • I am working in Microsoft Excel 2010 on the Money management template, and I am trying to update the account drop down box to change the examples to my own personal data. I have entered all my other data and am so far loving these spreadsheets, BUT I for the life of me can not, CAN NOT find where to update this? I have read the tipsheet and it states this is to the right…. I do not see anything…

    Can anyone help??? Please!
    Thank you!!!!

    • @Amcav, the list of accounts that is used in the dropdown box in the transactions sheet can be updated via the Help worksheet, directly “to the right” of the instructions. The list might look like a screenshot or something, but it is the actual range of cells used in the dropdown. Email me if this does not help.

  • I am having issues with the transactions not going into the reports? Any help would be appreciated

    • Hi Paula – assuming you are talking about the money management template, in the Transactions worksheet you may need to unhide the hidden columns and make sure that the formulas are copied down. You may have inserted or added news rows without copying formulas.

  • Mr. witmer:

    Thanks for letting us know this technique. List command is one that I haven´t ever used before ’cause I didn’nt how to.

  • We use that technique quite often to add simple Yes/No Switches in our financial model templates. Also creating more dynamic Dropdown lists becomes very easy when the list is linked to a cell range which can easily be updated.

  • Re the checkbook register.
    The “NUM” field has more possible entries than the “number dropdown list” . Where do the extra possible entries come from?
    Also, can you explain the entry in cell N4, as that may answer my other question.

    • @David, in the current version of the file checkbook-register.xlsx, the entries for the NUM drop-down list come from column M. Unhide all rows if you want to see the entire list (cell N4 says “unhide header rows…” and that refers to rows 5-11).


Leave a Comment