{"id":109,"date":"2012-01-29T16:04:02","date_gmt":"2012-01-29T16:04:02","guid":{"rendered":"http:\/\/xlfxs.com\/?p=109"},"modified":"2012-01-29T16:04:02","modified_gmt":"2012-01-29T16:04:02","slug":"excel-drop-downs-that-grow-with-you","status":"publish","type":"post","link":"https:\/\/xlfxs.com\/?p=109","title":{"rendered":"Excel Dynamic Drop-Downs that grow with you"},"content":{"rendered":"<p><span style=\"font-size: small;\">This will help you do a dynamic name to use on a drop down, a Form Listbox or Form Combobox.<br \/>\nThe screenshot almost says it all.<br \/>\nYou need 4 steps:<br \/>\n1- Create the name for a column in your Excel file, go to Names &gt; Define in Excel2003 or earlier, or go to Formulas &gt; Name Manager in Excel2007 and later.<br \/>\n2- Use the <em><strong>OFFSET<\/strong><\/em> + <em><strong>COUNTA<\/strong><\/em> function combination, formula used here is<\/span><\/p>\n<pre><span style=\"font-size: small;\">\n=OFFSET(Sheet2!$F$3,0,0,COUNTA(Sheet2!$F:$F),1)<\/span><\/pre>\n<p><span style=\"font-size: small;\">3- Draw your form listbox or combo box, or just do a regular data validation<br \/>\n4- tell that list (drop box) to use the name you just created.<\/span><\/p>\n<p>Now, to test it, add any thing below<\/p>\n<p><a href=\"http:\/\/xlfxs.com\/wp-content\/uploads\/2012\/01\/Drop_Downs-Grow.zip\"><img decoding=\"async\" class=\"aligncenter\" src=\"\/wp-content\/uploads\/2014\/12\/Download1.png\" alt=\"\" \/><\/a><\/p>\n\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>This will help you do a dynamic name to use on a drop down, a Form Listbox or Form Combobox. The screenshot almost says it all. You need 4 steps: 1- Create the name for a column in your Excel file, go to Names &gt; Define in Excel2003 or earlier, or go to Formulas &gt;&#8230;<\/p>\n<p class=\"more-link-wrap\"><a href=\"https:\/\/xlfxs.com\/?p=109\" class=\"more-link\">Read More<span class=\"screen-reader-text\"> &ldquo;Excel Dynamic Drop-Downs that grow with you&rdquo;<\/span> &raquo;<\/a><\/p>\n","protected":false},"author":1,"featured_media":110,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[75,22,3,36,47,6],"tags":[],"class_list":["post-109","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-counta","category-validation","category-formulas","category-lookup_and_references","category-offset","category-standard_functions"],"_links":{"self":[{"href":"https:\/\/xlfxs.com\/index.php?rest_route=\/wp\/v2\/posts\/109","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/xlfxs.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/xlfxs.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/xlfxs.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/xlfxs.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=109"}],"version-history":[{"count":0,"href":"https:\/\/xlfxs.com\/index.php?rest_route=\/wp\/v2\/posts\/109\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/xlfxs.com\/index.php?rest_route=\/wp\/v2\/media\/110"}],"wp:attachment":[{"href":"https:\/\/xlfxs.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=109"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/xlfxs.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=109"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/xlfxs.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=109"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}