{"id":3414,"date":"2017-09-29T08:26:04","date_gmt":"2017-09-29T14:26:04","guid":{"rendered":"https:\/\/www.vertex42.com\/blog\/?p=3414"},"modified":"2022-01-10T22:27:44","modified_gmt":"2022-01-11T04:27:44","slug":"dynamic-named-ranges","status":"publish","type":"post","link":"https:\/\/www.vertex42.com\/blog\/excel-formulas\/dynamic-named-ranges.html","title":{"rendered":"Dynamic Named Ranges in Excel"},"content":{"rendered":"<p>In Excel, a <i>dynamic range<\/i> is a reference (or a formula you use to create a reference) that may change based on user input or the results of another cell or function. It is more than just a fixed group of cells like $A1:$A100. I use dynamic ranges mostly for customizable drop-down lists. To avoid showing a bunch of blanks in the list, I use a formula to reference a range that extends to the last value in a column. The image below shows 4 different formulas that reference the range A2:A9 and can expand to include more rows if the user adds more categories to the list.<\/p>\n<div class=\"feature-image center aligncenter\" itemprop=\"image\" itemscope itemtype=\"https:\/\/schema.org\/ImageObject\"><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/feature\/dynamic-named-ranges-in-excel.png\" alt=\"Dynamic Named Ranges in Excel\" width=\"730\" height=\"382\" class=\"center imgshadow\"><meta itemprop=\"url\" content=\"https:\/\/cdn.vertex42.com\/blog\/images\/feature\/dynamic-named-ranges-in-excel.png\" \/><meta itemprop=\"width\" content=\"730\" \/><meta itemprop=\"height\" content=\"382\" \/><\/div>\n<p>When we talk about a <i>dynamic <b>named<\/b> range<\/i>, we're talking about using the <b>Name Manager<\/b> (via the Formula tab) to define a name for the formula, such as <b>categoryList<\/b>. We can then use that Name in other formulas or as the Source for <a href=\"https:\/\/www.vertex42.com\/blog\/help\/excel-help\/create-a-drop-down-list-in-excel.html\">drop-down lists<\/a>.<\/p>\n<p>This article isn't about the awesome advantages of using Excel Names, though there are many. Instead, it is about the many different formulas you can use to create the <i>dynamic named ranges<\/i>.<\/p>\n<p>To see these examples in action, download the Excel file below.<\/p>\n<p class=\"downloadlink\"><span class=\"icon16 excel\"><\/span><a href=\"\/Files\/examples\/DynamicRanges.xlsx\" onClick=\"ga('send','event', 'Downloads', 'Examples', 'DynamicRanges.xlsx');\" class=\"bigbtn\" rel=\"nofollow\"><b>Download the Example File<\/b><\/a> (DynamicRanges.xlsx)<\/p>\n<p><strong>Do you have a Dynamic Named Range challenge you need to solve?<\/strong> If you can't figure it out after reading this article, go ahead and ask your question by commenting below.<\/p>\n<div class=\"contents\">\n<p>This Page (contents):<\/p>\n<ul>\n<li><a href=\"#offset-function\">A) The OFFSET Function<\/a><\/li>\n<li><a href=\"#index-function\">B) The INDEX Function for a Non-Volatile Dynamic Range<\/a><\/li>\n<li><a href=\"#indirect-function\">C) The INDIRECT Function<\/a><\/li>\n<li><a href=\"#structured-references\">D) Structured References with Excel Tables<\/a><\/li>\n<li><a href=\"#choose-and-if-functions\">E) The CHOOSE and IF Functions<\/a><\/li>\n<li><a href=\"#last-value-position\">Find the Position of the LAST Value in the Column<\/a><\/li>\n<li><a href=\"#dynamic-print-area\">Bonus: Create a Dynamic Print Area<\/a><\/li>\n<li><a href=\"#not-in-google\">No Dynamic Named Ranges in Google Sheets<\/a><\/li>\n<\/ul>\n<\/div>\n<h2>Watch the Video<\/h2>\n<div itemprop=\"video\" itemscope itemtype=\"http:\/\/schema.org\/VideoObject\"><iframe title=\"Watch the Video\" width=\"720\" height=\"405\" src=\"https:\/\/www.youtube.com\/embed\/h9pctp6XCUs?rel=0\" frameborder=\"0\" allowfullscreen><\/iframe><meta itemprop=\"name\" content=\"Dynamic Named Ranges in Excel\"><meta itemprop=\"description\" content=\"Learn how to create a dynamic named range for customizable drop down lists in Excel. The video demonstrates how to do this within a purchase order template.\"><meta itemprop=\"thumbnailURL\" content=\"https:\/\/cdn.vertex42.com\/blog\/images\/thumbnails\/how-to-create-dynamic-named-ranges-in-excel-720x405.png\"><meta itemprop=\"embedURL\" content=\"https:\/\/www.youtube.com\/embed\/h9pctp6XCUs?rel=0\"><meta itemprop=\"uploadDate\" content=\"2017-10-11\"><\/div>\n<h2 id=\"offset-function\">A) The OFFSET Function<\/h2>\n<p>The most common function used to create a dynamic named range is probably the <a href=\"https:\/\/support.office.com\/en-us\/article\/OFFSET-function-c8de19ae-dd79-4b9b-a14e-b4d906d11b66\" target=\"_blank\" rel=\"nofollow noopener noreferrer\">OFFSET<\/a> function. It allows you to define a range with a specific number of rows and columns, starting from a <i>reference<\/i> cell. The syntax for the OFFSET function is:<\/p>\n<pre class=\"XLformula light\">=<span class=\"function\">OFFSET<\/span>(<span class=\"arg\">reference<\/span>,<span class=\"arg\">offset_rows<\/span>,<span class=\"arg\">offset_cols<\/span>,[<span class=\"arg\">height<\/span>],[<span class=\"arg\">width<\/span>])<\/pre>\n<p>The <i>offset_rows<\/i> and <i>offset_cols<\/i> values tell the function where you want the upper-left cell of your range to be, relative to the <i>reference<\/i> cell. The <i>height<\/i> and <i>width<\/i> values tell the function how many rows and columns you want to include in your range.<\/p>\n<h3>A Basic Customizable List<\/h3>\n<div class=\"alignright\"><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/ExcelTemplates\/Images\/scr_meal-list.gif\" alt=\"Meal List Worksheet\" width=\"199\" height=\"206\" class=\"align-right\" style=\"margin-left:10px;\"><\/div>\n<p>In the <a href=\"\/ExcelTemplates\/meal-planner.html\">meal planner<\/a> template, I have a list of meals that I use as the Source for many different drop-down lists. I want to allow the user to edit and add to the list, so the <b>dynamic named range<\/b> used by the drop-down needs to extend to the last value in the list. I could define the range as $A:$A (the entire column) or $A1:$A1000 (assuming they won't add more than 1000 items to the list), but then I'd end up with a bunch of blank cells at the end of my drop-down list.<\/p>\n<p>Using the OFFSET function, I define a range that starts at cell $A$1 and ends at the cell containing the <b>last value in the column<\/b>. If the last value in the column was on row 15, the formula for the named range would be:<\/p>\n<pre class=\"XLformula light\">=<span class=\"function\">OFFSET<\/span>($A$1,0,0,15,1)<\/pre>\n<p>To make this formula <b>dynamic<\/b>, I'll use a formula or reference in place of the <i>height<\/i> value (15) to specify how many rows I want the range to include. I expect only text values in this list, so I'll use the MATCH function to find the last text value in the column like this:<\/p>\n<pre class=\"XLformula light\">=<span class=\"function\">OFFSET<\/span>($A$1,0,0,<span class=\"function\">MATCH<\/span>(\"zzzz\",$A:$A),1)<\/pre>\n<p>There are many formulas you can use to determine the <i>height<\/i> or <i>width<\/i> of the range, such as COUNTA, COUNT, VLOOKUP, LOOKUP, MATCH or various array formulas. What formula you use may depend on whether you expect there to be blank cells in the list, error values (like #N\/A or #DIV\/0!), or data of different types (text or numeric or both), and whether calculation speed is an issue.<\/p>\n<p>Later in this article I'll talk more about the various formulas for finding the last value in the range. For now, we'll continue using MATCH.<\/p>\n<p><!--\n\n\n<p>You'll see a lot of people using COUNT or COUNTA to count the number of numeric or text values in a list, but I hardly ever use that technique because it assumes there aren't <b>blank<\/b> values in the list. I prefer to use functions like MATCH to find the position of the last value in the list.<\/p>\n\n\n--><\/p>\n<h3>A Robust Dynamic Range<\/h3>\n<p>When you allow a user to edit a list, they may end up deleting the first row in the list, or they might insert a row above the first row in the list (between the label row and the first item). To create a formula that works in spite of these actions by the user, I like to use a label for the <i>reference<\/i> and include the label in the <i>range<\/i>, as shown in the following examples.<\/p>\n<div class=\"center aligncenter\"><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-formulas\/reference-for-robust-dynamic-named-range.png\" alt=\"Reference for Robust Dynamic Named Ranges\" class=\"border imgshadow\"><\/div>\n<h4>Example 1: Include the label in the list<\/h4>\n<pre class=\"XLformula light\">=<span class=\"function\">OFFSET<\/span>(<span class=\"arg\">reference<\/span>,0,0,<span class=\"function\">MATCH<\/span>(\"zzz\",<span class=\"arg\">range<\/span>),1)<\/pre>\n<h4>Example 2: Exclude the label from the list<\/h4>\n<pre class=\"XLformula light\">=<span class=\"function\">OFFSET<\/span>(<span class=\"arg\">reference<\/span>,<span style=\"color:#900;font-weight:bold;\">1<\/span>,0,<span class=\"function\">MATCH<\/span>(\"zzz\",<span class=\"arg\">range<\/span>)<span style=\"color:#900;font-weight:bold;\">-1<\/span>,1)<\/pre>\n<p>Notice in the second example that <i>reference<\/i> and <i>range<\/i> have not changed. To exclude the label from the list, we are using an offset of 1 row and subtracting one row from the number returned by the MATCH function.<\/p>\n<h2 id=\"index-function\">B) The INDEX Function for a Non-Volatile Dynamic Range<\/h2>\n<p>A <a href=\"https:\/\/www.vertex42.com\/blog\/excel-formulas\/volatile-functions.html\">volatile function<\/a> within a cell or named range is recalculated every time any cell in the worksheet recalculates (instead of just when one of the function's arguments changes). Also, any formula that depends on a cell containing a volatile function is also recalculated. If you have a large file with thousands of inefficient formulas that reference a cell containing a volatile function, Excel may seem to take forever to recalculate. OFFSET is a volatile function. INDEX is not, so some people prefer to use INDEX for dynamic ranges.<\/p>\n<p>The <a href=\"https:\/\/support.office.com\/en-us\/article\/INDEX-function-A5DCF0DD-996D-40A4-A822-B56B061328BD\" target=\"_blank\" rel=\"nofollow noopener noreferrer\">INDEX<\/a> function can return the reference to a range or cell instead of just the value. That means that just like OFFSET, you can use INDEX in many places where Excel is expecting a cell reference. For example, to create the range <b>A1:A4<\/b> you can use <b>INDEX(A:A,1):INDEX(A:A,4)<\/b>.<\/p>\n<p>We can use INDEX to create the same dynamic ranges shown in the OFFSET examples:<\/p>\n<h4>Example 1: Include the label in the list<\/h4>\n<pre class=\"XLformula light\">=<span class=\"function\">INDEX<\/span>(<span class=\"arg\">range<\/span>,1):<span class=\"function\">INDEX<\/span>(<span class=\"arg\">range<\/span>,<span class=\"function\">MATCH<\/span>(\"zzz\",<span class=\"arg\">range<\/span>))<\/pre>\n<h4>Example 2: Exclude the label from the list<\/h4>\n<pre class=\"XLformula light\">=<span class=\"function\">INDEX<\/span>(<span class=\"arg\">range<\/span>,<span style=\"color:#900;font-weight:bold;\">2<\/span>):<span class=\"function\">INDEX<\/span>(<span class=\"arg\">range<\/span>,<span class=\"function\">MATCH<\/span>(\"zzz\",<span class=\"arg\">range<\/span>))<\/pre>\n<p>To exclude the label (the first cell in the <i>range<\/i>), the only change we needed to make was the \"2\" in INDEX(<i>range<\/i>,2). Using INDEX(<i>range<\/i>,1) and INDEX(<i>range<\/i>,2) instead of just a cell reference makes the formula more robust. It prevents the problem of having the reference changed to #REF! if the reference row gets deleted.<\/p>\n<div class=\"note-box\">\n<p><span class=\"note-label\">NOTE<\/span>When using INDEX, the formulas cannot be entered directly as the Source for a drop-down list. But, if you create the formula as a dynamic <b>named<\/b> range, you can use the name as the Source for the drop-down list.<\/p>\n<\/div>\n<p><!--\n\n\n<p>Question: <i>If OFFSET is volatile and INDEX is not, shouldn't we just use INDEX?<\/i><br \/>Answer: I've never had a problem with OFFSET being volatile. In fact, if you are using a dynamic named range only to populate a drop-down list, then it doesn't seem to matter if the formula for the dynamic range is volatile or not. I could be wrong, but I think that the formula used in the Source for a drop-down list is only recalculated when you access the drop-down list.<\/p>\n\n\n--><\/p>\n<p>By the way, I learned about using the INDEX function to make a non-volatile dynamic range from Mynda Treacy at <a href=\"http:\/\/www.myonlinetraininghub.com\/excel-dynamic-named-ranges\" target=\"_blank\" rel=\"noopener noreferrer\">MyOnlineTrainingHub.com<\/a> and Debra Dalgleish at <a href=\"http:\/\/blog.contextures.com\/archives\/2011\/02\/25\/excel-function-friday-index-for-dynamic-range\/\" target=\"_blank\" rel=\"noopener noreferrer\">Contextures.com<\/a>.<\/p>\n<div class=\"template-feature\">\n<p><a href=\"https:\/\/www.vertex42.com\/ExcelTemplates\/purchase-order-price-list.html\"><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/ExcelTemplates\/Images\/purchase-order-with-price-list_180.png\" alt=\"Purchase Order with Price List\" width=\"139\" height=\"180\" style=\"float:left;margin-right:1em;\" class=\"imgshadow\"><b>Purchase Order with Price List<\/b><\/a><\/p>\n<p>See Dynamic Named Ranges in action. This template uses multiple customizable lists for selecting items descriptions, vendor and ship to addresses, and other options. The item description list uses the OFFSET formula and the other lists use the INDEX formula.<\/p>\n<div class=\"clear\"><\/div>\n<\/div>\n<h2 id=\"indirect-function\">C) The INDIRECT Function<\/h2>\n<p>The <a href=\"https:\/\/support.office.com\/en-us\/article\/INDIRECT-function-474B3A3A-8A26-4F44-B491-92B6306FA261\" target=\"_blank\" rel=\"nofollow noopener noreferrer\">INDIRECT<\/a> function is another awesome function for creating dynamic ranges. It is also a <i>volatile<\/i> function, but like I mentioned above, that might not matter. The INDIRECT function lets you define a reference from a text string, and you can use concatenation to create the text string, like this:<\/p>\n<pre class=\"XLformula light\">=<span class=\"function\">INDIRECT<\/span>(\"Sheet1!A1:A\"&amp;<span class=\"arg\">row_num<\/span>)<\/pre>\n<p>In the example shown at the top of this post, to create the reference \"Sheet1!A2:A9\" we replace <i>row_num<\/i> with MATCH(\"zzz\",$A:$A) to return the row number of the last text value.<\/p>\n<p><b>Important:<\/b> When using INDIRECT to reference a range on a different worksheet, don't forget to include the worksheet name in the string. When you enter a reference in the <i>Refers To<\/i> field via the Name Manager, Excel will automatically change $A:$A to \"Sheet1!$A:$A\" but it won't know how to change your text string. This is one of the cons to using INDIRECT because if you change the name of the worksheet, you'll need to remember to update your INDIRECT formula.<\/p>\n<h2 id=\"structured-references\">D) Structured References with Excel Tables<\/h2>\n<p>Beginning with Excel 2007, Microsoft introduced a new way to work with data in what are called <a href=\"https:\/\/support.office.com\/en-us\/article\/Create-or-delete-an-Excel-table-e81aa349-b006-4f8a-9806-5af9df0ac664?ui=en-US&rs=en-US&ad=US\" target=\"_blank\" rel=\"noopener noreferrer\">Excel Tables<\/a>, not to be confused with <a href=\"https:\/\/support.office.com\/en-us\/article\/Calculate-multiple-results-by-using-a-data-table-e95e2487-6ca6-4413-ad12-77542a5ea50b\">Data Tables<\/a>, or other generic uses of the term <i>table<\/i> (yes, it is confusing).<\/p>\n<p>If you use an official \"Excel Table,\" you can use a type of reference known as a <a href=\"https:\/\/support.office.com\/en-us\/article\/Using-structured-references-with-Excel-tables-f5ed2452-2337-4f71-bed3-c8ae6d2b276e\" target=\"_blank\" rel=\"noopener noreferrer\">structured reference<\/a>. The structured reference uses the table name and the column labels like this:<\/p>\n<pre class=\"XLformula light\">=Table1[Categories]<\/pre>\n<p>You don't need to know how to type the correct structured reference. While you are entering a formula, if you select the column in the Table, Excel will automatically change your reference to the structured reference.<\/p>\n<div class=\"alignright\"><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/screenshots\/excel-table-resize-handle.png\" alt=\"Excel Table Resize Handle\" width=\"393\" height=\"274\" class=\"imgshadow\"><\/div>\n<p>The reason these types of references can be considered <b>dynamic<\/b> is because of the unique way that table references behave when you expand or contract the table. Meaning, if you extend the table down by dragging the resize handle in the lower-right corner of the table, the reference =Table1[Categories] will automatically include the new rows.<\/p>\n<p>So, instead of using an OFFSET formula to extend a range to the last value in a customizable list, we can let the user change the size of the list using the drag handle. Of course, this requires the user to know how to use Tables and the drag handle. That is one of the reasons I don't often use this technique in my templates (that, and lack of compatibility with OpenOffice and Google Sheets).<\/p>\n<h2 id=\"choose-and-if-functions\">E) The CHOOSE and IF Functions<\/h2>\n<p>Although I don't use the CHOOSE function or nested IF formulas to create lists of variable length, they are pretty powerful functions for creating other types of dynamic ranges. For example, the two formulas below both return a different range (<i>range_1<\/i>, <i>range_2<\/i> or <i>range_3<\/i>) based on a given <i>number<\/i>:<\/p>\n<pre class=\"XLformula light\">=<span class=\"function\">CHOOSE<\/span>(<span class=\"arg\">number<\/span>,<span class=\"arg\">range_1<\/span>,<span class=\"arg\">range_2<\/span>,<span class=\"arg\">range_3<\/span>,...)<\/pre>\n<pre class=\"XLformula light\">=<span class=\"function\">IF<\/span>(<span class=\"arg\">number<\/span>=1,<span class=\"arg\">range_1<\/span>,<span class=\"function\">IF<\/span>(<span class=\"arg\">number<\/span>=2,<span class=\"arg\">range_2<\/span>,<span class=\"function\">IF<\/span>(<span class=\"arg\">number<\/span>=3,<span class=\"arg\">range_3<\/span>)))<\/pre>\n<p>The CHOOSE function is basically a shortcut for the nested IF formula in this case. When possible, I prefer using CHOOSE over nested IF formulas (if only to avoid having a million closing parentheses at the end of the formula ))))))))))).<\/p>\n<p><b>See CHOOSE in action:<\/b> My article \"<a href=\"https:\/\/www.vertex42.com\/blog\/help\/excel-help\/create-a-drop-down-list-in-excel.html#dependent\">Create a Drop Down List in Excel<\/a>\" shows an example using CHOOSE to create a dependent drop down list.<\/p>\n<h2 id=\"last-value-position\">Find the Position of the LAST Value in the Column<\/h2>\n<p>When using a dynamic named range for lists of variable length, we need to know how to find the numeric <b>position<\/b> of the last value in the column. In the examples above we used MATCH(\"zzz\",<i>range<\/i>) for text values, but for numeric data or a combination of text and numeric data we may need to use something different. The formulas below cover all the scenarios I've come across.<\/p>\n<p>The following image will be used for the example. It shows the <i>reference<\/i> and <i>range<\/i> and the position numbers of the values in the range.<\/p>\n<p class=\"center aligncenter\"><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-formulas\/position-of-last-value-in-range.png\" width=\"326\" height=\"212\" alt=\"Position of Last Value in Range\" class=\"imgshadow\"><\/p>\n<h3>1. Find the Position of the Last Numeric Value<\/h3>\n<p>The following formulas will allow the range to include blanks, error values, and text values, but the formulas will only return the position of the last <b>numeric<\/b> value. The result would be 4 in the example above.<\/p>\n<pre class=\"XLformula light\">=<span class=\"function\">MATCH<\/span>(1E+100,<span class=\"arg\">range<\/span>,1)<\/pre>\n<pre class=\"XLformula light\">=<span class=\"function\">LOOKUP<\/span>(42,1\/<span class=\"function\">ISNUMBER<\/span>(<span class=\"arg\">range<\/span>),<span class=\"function\">ROW<\/span>(<span class=\"arg\">range<\/span>)-<span class=\"function\">ROW<\/span>(<span class=\"arg\">reference<\/span>)+1)<\/pre>\n<p>See my article about <a href=\"https:\/\/www.vertex42.com\/blog\/excel-formulas\/vlookup-and-index-match-examples.html\">VLOOKUP and INDEX-MATCH<\/a> for a more detailed explanation of these MATCH and LOOKUP formulas. LOOKUP does not behave the same way in OpenOffice and Google Sheets. So, for maximum compatibility, I still prefer to use MATCH.<\/p>\n<p>The value 9.99999999999999E+307 could be used for the MATCH function because it is the largest numeric value that can be stored in a cell, but that is kind of overkill. I prefer to use something more concise like 1E+100 or 10^100 (a googol).<\/p>\n<h3>2. Find the Position of the Last Text Value<\/h3>\n<p>The following formulas will allow the range to include blanks, error values, and numeric values, but the formulas will only return the position of the last <b>text<\/b> value (row 5 in the example above).<\/p>\n<pre class=\"XLformula light\">=<span class=\"function\">MATCH<\/span>(\"zzzz\",<span class=\"arg\">range<\/span>,1)<\/pre>\n<pre class=\"XLformula light\">=<span class=\"function\">LOOKUP<\/span>(42,1\/<span class=\"function\">ISTEXT<\/span>(<span class=\"arg\">range<\/span>),<span class=\"function\">ROW<\/span>(<span class=\"arg\">range<\/span>)-<span class=\"function\">ROW<\/span>(<span class=\"arg\">reference<\/span>)+1)<\/pre>\n<p><b>Note:<\/b> Using \"zzzz\" for the lookup value is not fool proof. The Greek, Cyrillic, Hebrew, and Arabic Unicode characters (and possibly other Unicode characters) come after z in the sort order. You might try using a high-value <a href=\"https:\/\/www.vertex42.com\/blog\/help\/excel-help\/using-unicode-character-symbols-in-excel.html\">unicode symbol<\/a> such as &#128511; if you expect the list to contain symbols.<\/p>\n<h3>3. Find the Position of the Last Non-Blank Value<\/h3>\n<p>When we want to return the last non-blank value, we can use the largest position returned by the two MATCH formulas listed above, or we can use the LOOKUP formula.<\/p>\n<pre class=\"XLformula light\">=<span class=\"function\">MAX<\/span>(<span class=\"function\">MATCH<\/span>(9E+307,<span class=\"arg\">range<\/span>),<span class=\"function\">MATCH<\/span>(\"&#128511;\",<span class=\"arg\">range<\/span>))<\/pre>\n<pre class=\"XLformula light\">=<span class=\"function\">LOOKUP<\/span>(42, 1\/<span class=\"function\">NOT<\/span>(<span class=\"function\">ISBLANK<\/span>(<span class=\"arg\">range<\/span>)), <span class=\"function\">ROW<\/span>(<span class=\"arg\">range<\/span>)-<span class=\"function\">ROW<\/span>(<span class=\"arg\">reference<\/span>)+1 )<\/pre>\n<p>Note that the <i>empty<\/i> string &quot;&quot; is not blank. It is treated as a <b>text<\/b> value.<\/p>\n<p>A cell containing a formula is not blank, even if the formula returns an empty string. So, use the next method if you want to find the last non-<i>empty<\/i> value when you are using formulas that might return an empty string.<\/p>\n<h3>4. Find the Position of the Last Non-Empty Value \"\"<\/h3>\n<p>In many practical applications (like <a href=\"\/Calculators\/loan-calculators.html\">loan calculators<\/a>), we may not necessarily want to find the last non-<i>blank<\/i> cell ( meaning no values or formulas), but instead may want to find the last non-<i>empty<\/i> cell.<\/p>\n<p>If we define \"empty\" as the empty string \"\", then we can return the last non-empty value using the formula below. This might look like an <a href=\"\/blog\/excel-formulas\/array-formula-examples.html\">array formula<\/a>, but it isn't, thanks to the way LOOKUP works (See <a href=\"https:\/\/exceljet.net\/formula\/get-value-of-last-non-empty-cell\" target=\"_blank\" rel=\"noopener noreferrer\">this article<\/a> on exceljet.net).<\/p>\n<pre class=\"XLformula light\">=<span class=\"function\">LOOKUP<\/span>(42, 1\/(<span class=\"arg\">range<\/span>&lt;&gt;\"\"), <span class=\"function\">ROW<\/span>(<span class=\"arg\">range<\/span>)-<span class=\"function\">ROW<\/span>(<span class=\"arg\">reference<\/span>)+1 )<\/pre>\n<p>As I mentioned before, LOOKUP does not work the same way in OpenOffice and Google Sheets, but SUMPRODUCT provides a good solution.<\/p>\n<pre class=\"XLformula light\">=<span class=\"function\">SUMPRODUCT<\/span>(<span class=\"function\">MAX<\/span>((<span class=\"arg\">range<\/span>&lt;&gt;\"\")*(<span class=\"function\">ROW<\/span>(<span class=\"arg\">range<\/span>)-<span class=\"function\">ROW<\/span>(<span class=\"arg\">reference<\/span>)+1)))<\/pre>\n<p>The SUMPRODUCT function allows you to avoid entering the formula as an <a href=\"\/blog\/excel-formulas\/array-formula-examples.html\">array formula<\/a> (not having to use Ctrl+Shift+Enter). Inside the SUMPRODUCT function, we are multiplying the boolean (FALSE=0 or TRUE=1) result for the (<i>range<\/i>&lt;&gt;\"\") comparison by the relative row number, so that the maximum result is the row number of the last blank cell.<\/p>\n<h2 id=\"dynamic-print-area\">Bonus: Create a Dynamic Print Area<\/h2>\n<p>When you create a print area in Excel via Page Layout &gt; Print Area &gt; Set Print Area, Excel actually creates a special named range called <i>Print_Area<\/i>. You can then go to Formulas &gt; Name Manager and edit that named range to use a formula like this:<\/p>\n<pre class=\"XLformula light\">=<span class=\"function\">OFFSET<\/span>(<span class=\"arg\">start_cell<\/span>,0,0,<span class=\"arg\">rows<\/span>,<span class=\"arg\">columns<\/span>)<\/pre>\n<p>This is a technique I use in some financial templates like the <a href=\"https:\/\/www.vertex42.com\/Calculators\/home-mortgage-calculator.html\">Home Mortgage Calculator<\/a> to limit the print area based on the length of the payment schedule.<\/p>\n<p><i>The catch<\/i>: If you edit the Page Layout (margins, scaling, etc.), the <i>Print_Area<\/i> reference is changed to a regular reference. I'm not sure why the formula is removed, but this is basically a bug in Excel that has yet to be resolved. To avoid recreating the formula, I typically copy the formula and paste it into a text editor so that I can easily recreate the dynamic print area after I'm done making changes to the page layout.<\/p>\n<h2 id=\"not-in-google\">No Dynamic Named Ranges in Google Sheets<\/h2>\n<p>Perhaps things will change in the future, but as of my last update (Sep 28, 2017), you cannot create named formulas using the Named Ranges feature in Google Sheets. This means that although you can use a Named Range for a drop-down list, you can't create a <b>Dynamic<\/b> Named Range using a formula in Google Sheets.<\/p>\n<p><a href=\"https:\/\/www.benlcollins.com\/formula-examples\/dynamic-named-ranges\/\" target=\"_blank\" rel=\"noopener noreferrer\">Ben Collins<\/a> shows how you can use a cell to create a text string like \"A1:A\"&amp;<i>row_num<\/i>, name the cell, and then use INDIRECT(theName) to get some of the function of a dynamic named range. Unfortunately, that still doesn't work for drop-down lists, because you can't use INDIRECT(theCell) as the Range for a drop-down list.<\/p>\n<p><!--        REFERENCES          --><\/p>\n<div class=\"refblock\">\n<h3>References Related to Dynamic Named Ranges<\/h3>\n<ul>\n<li><a href=\"https:\/\/www.ozgrid.com\/Excel\/DynamicRanges.htm\" target=\"_blank\" rel=\"nofollow noopener noreferrer\">Dynamic Named Ranges<\/a> at <i>ozgrid.com<\/i> - I think Dave Hawley's site is the first place I saw the term \"dynamic named range\" used. Now, you can find articles on the subject at most Excel help sites.<\/li>\n<li><a href=\"https:\/\/support.office.com\/en-us\/article\/Using-structured-references-with-Excel-tables-f5ed2452-2337-4f71-bed3-c8ae6d2b276e\">Using structured references with Excel tables<\/a> at <i>support.office.com<\/i><\/li>\n<li><a href=\"https:\/\/www.contextures.com\/xlNames01.html\" target=\"_blank\" rel=\"noopener noreferrer\">Excel Named Ranges<\/a> at <i>contextures.com<\/i> - A great article by Debra Dalgleish about how to create and use named ranges in Excel.<\/li>\n<\/ul>\n<\/div>\n<p><!-- End Ref Block --><\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>A Dynamic Named Range is an awesome way to reference customizable lists. This article explains how to use OFFSET, INDEX, INDIRECT, CHOOSE, and IF to create dynamic ranges. It also explains formulas for finding the position of the last value in a range.<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":"","_links_to":"","_links_to_target":""},"categories":[79],"tags":[],"class_list":{"0":"post-3414","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-excel-formulas"},"_links":{"self":[{"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/posts\/3414","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=3414"}],"version-history":[{"count":0,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/posts\/3414\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/media?parent=3414"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/categories?post=3414"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/tags?post=3414"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}