{"id":2524,"date":"2013-04-29T11:24:26","date_gmt":"2013-04-29T17:24:26","guid":{"rendered":"https:\/\/www.vertex42.com\/blog\/?p=2524"},"modified":"2017-09-29T08:36:25","modified_gmt":"2017-09-29T14:36:25","slug":"dynamic-drop-down-lists","status":"publish","type":"post","link":"https:\/\/www.vertex42.com\/blog\/help\/excel-help\/dynamic-drop-down-lists.html","title":{"rendered":"Dynamic Drop-Down Lists for Dates and Check Numbers"},"content":{"rendered":"

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<\/strong>. This technique came about through the use of the Money Management<\/a> template, so I will use that template as an example.<\/p>\n

First, if you aren't familiar with how to use data validation to add an in-cell drop-down, read my article Create a Drop-Down List in Excel<\/a>.<\/p>\n

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

\"DynamicDynamic drop-down list for Dates<\/span><\/div>\n
\"DynamicDynamic drop-down list for Check Numbers<\/span><\/div>\n
<\/div>\n

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.<\/p>\n

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).<\/p>\n

Setting up the Dynamic Drop-Down Lists<\/h2>\n

Step 1: Create the lists<\/h3>\n

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<\/strong> is adding +1 to the current maximum check number in the Num<\/strong> column. Cell C2<\/strong> is finding the most recent date in the Date<\/strong> column. The rest of the items in the list are just calculated from those two cells as shown.<\/p>\n

\"FormulasFormulas for the drop down lists<\/div>\n

Step 2: Name the lists<\/h3>\n

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<\/b> to name the ranges as shown in the next image.<\/p>\n

\"CreatingNaming the lists<\/span><\/div>\n

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.<\/p>\n

Step 3: Create the in-cell drop-downs<\/h3>\n

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

\"DataData Validation Settings<\/span><\/div>\n
\n
Important<\/strong>: To Avoid the Data Validation Error Message ...<\/div>\n

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<\/b> tab. This allows you to still enter any check number of date that you want, without Excel popping up an error warning.<\/p>\n<\/div>\n

Something Really Fancy<\/h2>\n

One important thing to realize when creating dynamic drop-down lists is that the Source value must be a range reference. Even if you use a named range, it cannot be a formula like =A1:A10+2 which returns the values of the range. The reference CAN be a dynamic named range<\/a> using OFFSET, INDEX, or INDIRECT.<\/p>\n

So, let's say that I want the dates in a drop-down list to be ±5 days from the date that is currently in the cell. If the cell is empty, then I want it to be ±5 days from today's date.<\/p>\n

\"Drop-Down<\/div>\n

To make this happen, you can create a helper worksheet (called \"Dates\") where column A:A is just a column of numbers 1:55000 formatted as dates (A1=1\/1\/1900, A2=1\/2\/1901, etc.), recognizing that the stored value for a date is simply a number (1\/1\/2017 is 42736).<\/p>\n

Let's say you have a date stored in cell C8. Select cell C8 and go Formulas > Name Manager and create a named range \"rel_date_list\" using the following formula in the Refers To field (it is very important that C8 be a relative reference):<\/p>\n

=INDEX<\/span>( Dates!$A:$A, IF(C8=\"\",TODAY<\/span>(),C8)-5, 1 ):INDEX<\/span>( Dates!$A:$A, IF(C8=\"\",TODAY<\/span>(),C8)+5, 1 )<\/pre>\n
Now, you can use =rel_date_list as the Source for a data validation drop-down list, and the list of dates in the drop-down will be ±5 days from the date in cell C8. Because you used a relative reference for the named range, you can copy\/paste cell C8 to use this feature in other cells within the same worksheet.<\/p>\n
When creating the named range, choose the current sheet as the Scope because after you create the name, the sheet name will automatically be added to the relative reference, and rel_date_list is only applicable to the worksheet where you defined it.<\/p>\n","protected":false},"excerpt":{"rendered":"
Learn how to create a drop-down list to select the next date or next check number when updating lists of transactions or adding log entries.<\/p>\n","protected":false},"author":3,"featured_media":2564,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":"","_links_to":"","_links_to_target":""},"categories":[37],"tags":[61,59,62,60],"class_list":{"0":"post-2524","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","7":"category-excel-help","8":"tag-data-validation","9":"tag-drop-down-list","10":"tag-form","11":"tag-lists"},"_links":{"self":[{"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/posts\/2524","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/comments?post=2524"}],"version-history":[{"count":0,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/posts\/2524\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/media\/2564"}],"wp:attachment":[{"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/media?parent=2524"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/categories?post=2524"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/tags?post=2524"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}