{"id":210,"date":"2012-02-07T04:32:03","date_gmt":"2012-02-07T10:32:03","guid":{"rendered":"http:\/\/xlfxs.com\/?p=210"},"modified":"2012-02-07T04:32:03","modified_gmt":"2012-02-07T10:32:03","slug":"index-counta-names-and-listbox-to-have-active-form-reading-from-table","status":"publish","type":"post","link":"https:\/\/xlfxs.com\/?p=210","title":{"rendered":"Dynamic selection list"},"content":{"rendered":"<p>Using Index and CountA Formulas, Names and Forms &gt; ListBox to have a list with connected formulas<\/p>\n<p>Can also be used as form to read from a large table in another sheet<\/p>\n<p>&nbsp;<\/p>\n<p>After creating two names, one is &#8220;Months&#8221; having:<\/p>\n<pre>=OFFSET(Sheet1!$B$5,0,0,COUNTA(Sheet1!$B:$B)-2,1)<\/pre>\n<p>And the other one is &#8220;Month_All&#8221; having:<\/p>\n<pre>=OFFSET(Sheet1!$B$5,0,0,COUNTA(Sheet1!$B:$B)-2,3)<\/pre>\n<p>Draw a listbox using &#8220;Developer toolbar&#8221;, then make its input list as &#8220;Months&#8221; name we did above, then use these formulas:<\/p>\n<p><!--more--><\/p>\n<pre>=INDEX(Months_ALL,E4,1)<\/pre>\n<pre>=INDEX(Months_ALL,E4,2)<\/pre>\n<pre>=INDEX(Months_ALL,E4,3)<\/pre>\n<p>To lookup the other fields once you select from the listbox<\/p>\n<p>You can create a combobox from that &#8220;Developer toolbar&#8221; with exactly same features<\/p>\n<p><a href=\"http:\/\/xlfxs.com\/wp-content\/uploads\/2012\/02\/ListBox.zip\"><img decoding=\"async\" class=\"aligncenter\" src=\"\/wp-content\/uploads\/2014\/12\/Download1.png\" alt=\"\"><\/a><\/p>\n\n","protected":false},"excerpt":{"rendered":"<p>Using Index and CountA Formulas, Names and Forms &gt; ListBox to have a list with connected formulas Can also be used as form to read from a large table in another sheet &nbsp; After creating two names, one is &#8220;Months&#8221; having: =OFFSET(Sheet1!$B$5,0,0,COUNTA(Sheet1!$B:$B)-2,1) And the other one is &#8220;Month_All&#8221; having: =OFFSET(Sheet1!$B$5,0,0,COUNTA(Sheet1!$B:$B)-2,3) Draw a listbox using &#8220;Developer&#8230;<\/p>\n<p class=\"more-link-wrap\"><a href=\"https:\/\/xlfxs.com\/?p=210\" class=\"more-link\">Read More<span class=\"screen-reader-text\"> &ldquo;Dynamic selection list&rdquo;<\/span> &raquo;<\/a><\/p>\n","protected":false},"author":1,"featured_media":213,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[14,75,3,84,36,44,6],"tags":[],"class_list":["post-210","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-activex-controls","category-counta","category-formulas","category-index","category-lookup_and_references","category-names","category-standard_functions"],"_links":{"self":[{"href":"https:\/\/xlfxs.com\/index.php?rest_route=\/wp\/v2\/posts\/210","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=210"}],"version-history":[{"count":0,"href":"https:\/\/xlfxs.com\/index.php?rest_route=\/wp\/v2\/posts\/210\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/xlfxs.com\/index.php?rest_route=\/wp\/v2\/media\/213"}],"wp:attachment":[{"href":"https:\/\/xlfxs.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=210"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/xlfxs.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=210"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/xlfxs.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=210"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}