{"id":3116,"date":"2016-05-30T23:21:29","date_gmt":"2016-05-30T21:21:29","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=3116"},"modified":"2023-11-30T21:34:41","modified_gmt":"2023-11-30T20:34:41","slug":"indirect-function-excel","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/indirect-function-excel\/","title":{"rendered":"INDIRECT Function in Excel"},"content":{"rendered":"<p><em><strong>What does it do?<\/strong><\/em><\/p>\n<p>Returns a reference to a range. The referenced range can be a cell, a range of cells, or a named range.<\/p>\n<p><em><strong>Formula breakdown:<\/strong><\/em><\/p>\n<p>=INDIRECT(<span style=\"color: #0000ff;\">ref_text<\/span>, <span style=\"color: #ff0000;\">[a1]<\/span>)<\/p>\n<p><em><strong>What it means:<\/strong><\/em><\/p>\n<p>=INDIRECT(<span style=\"color: #0000ff;\">Return the referenced range<\/span>, <span style=\"color: #ff0000;\">Omit if the reference is an <em>A1<\/em> style <strong>or<\/strong> enter FALSE if it is a <em>R1C1<\/em> style<\/span>)<\/p>\n<hr \/>\n<p>The INDIRECT function mystifies lots of Excel users and\u00a0one that does not get that much fan fare, but I am about to change that for you!<\/p>\n<p>To be totally honest, I wasn&#8217;t a big user of the INDIRECT function, but after seeing the various ways that it can be applied in to an Excel workbook, Financial Model or Excel Dashboard, I was hooked!<\/p>\n<p>I will take you on this series in explaining how this function works, starting from the basics and moving on to real life practical uses that will take your Excel skills\u00a0to another level!<br \/>\n<iframe src=\"https:\/\/www.youtube.com\/embed\/oU9XlTusH8w?rel=0 \" width=\"800\" height=\"400\" frameborder=\"0\" allowfullscreen=\"allowfullscreen\"><span data-mce-type=\"bookmark\" style=\"display: inline-block; width: 0px; overflow: hidden; line-height: 0;\" class=\"mce_SELRES_start\">?<\/span><\/iframe><\/p>\n<p><a href=\"https:\/\/www.youtube.com\/c\/MyExcelOnlinecomtutorials?sub_confirmation=1\" target=\"_blank\" rel=\"noopener noreferrer nofollow\"><img loading=\"lazy\" decoding=\"async\" class=\"alignmiddle wp-image-111468 size-full\" style=\"vertical-align: middle;\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/04\/1.yt_.png\" alt=\"\" title=\"\"><\/a><br \/>\n<a class=\"IRPP_minimalist icon-download\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/Indirect-Intro.xlsx\" target=\"_blank\"><span class=\"ctaText px-3 text-capitalize\">Download excel workbook<\/span><span class=\"postTitle px-3\">Indirect-Intro.xlsx<\/span><\/a><\/p>\n<p>&nbsp;<\/p>\n<div style=\"text-align: center;\">\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_57_1 counter-flat ez-toc-counter ez-toc-transparent ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title \" >Table of Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li><a href=\"#\" class=\"scroll-to-download\">Free Practice Workbook<\/a><\/li><li class='ez-toc-page-1'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/indirect-function-excel\/#REFERENCED_RANGE_IS_A_CELL\" title=\"REFERENCED RANGE IS A CELL\">REFERENCED RANGE IS A CELL<\/a><\/li><li class='ez-toc-page-1'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/indirect-function-excel\/#REFERENCE_A_RANGE_OF_CELLS\" title=\"REFERENCE A RANGE OF\u00a0CELLS\">REFERENCE A RANGE OF\u00a0CELLS<\/a><\/li><li class='ez-toc-page-1'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/indirect-function-excel\/#REFERENCE_IS_A_NAMED_RANGE\" title=\"REFERENCE IS A NAMED RANGE\u00a0\">REFERENCE IS A NAMED RANGE\u00a0<\/a><\/li><\/ul><\/nav><\/div>\n<h2 style=\"text-align: center;\"><span class=\"ez-toc-section\" id=\"REFERENCED_RANGE_IS_A_CELL\"><\/span><strong><span style=\"color: #ff0000;\">REFERENCED RANGE IS A CELL<\/span><\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<\/div>\n<div style=\"text-align: center;\">\n<h2 style=\"text-align: center;\">=INDIRECT(<span style=\"color: #0000ff;\">G9<\/span>)<\/h2>\n<\/div>\n<p>When the referenced range is a cell,\u00a0the INDIRECT function will go and\u00a0return the content of the referenced cell.<\/p>\n<p>Say we enter in cell <strong>G9<\/strong> the following A1 style: <em><strong>D10<\/strong><\/em><\/p>\n<p>In another cell we enter <strong>=INDIRECT(G9)<\/strong><\/p>\n<p>This will indirectly return the value that resides in cell D10, which is the number <strong>32<\/strong> in our example below:<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/cell-ref.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"INDIRECT Function in Excel\"  class=\"alignnone size-full wp-image-3119\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/cell-ref.png\" alt=\"INDIRECT Function in Excel\" width=\"510\" height=\"317\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/cell-ref.png 510w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/cell-ref-300x186.png 300w\" sizes=\"(max-width: 510px) 100vw, 510px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<div style=\"text-align: center;\">\n<h2 style=\"text-align: center;\"><span class=\"ez-toc-section\" id=\"REFERENCE_A_RANGE_OF_CELLS\"><\/span><strong><span style=\"color: #ff0000;\">REFERENCE A RANGE OF\u00a0CELLS<\/span><\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<\/div>\n<div style=\"text-align: center;\">\n<h2 style=\"text-align: center;\">=SUM(INDIRECT(<span style=\"color: #0000ff;\">C9:E9<\/span>))<\/h2>\n<\/div>\n<p>When the referenced range is a range of cells,\u00a0the INDIRECT function will go and\u00a0return the content of the referenced cells.<\/p>\n<p>We can then enter a SUM function which will total the referenced cells.<\/p>\n<p>Say we enter in cell <strong>I9<\/strong> the following A1 style:\u00a0<em><strong>C9:E9<\/strong><\/em><\/p>\n<p>In another cell we enter <strong>=INDIRECT(I9)<\/strong><\/p>\n<p>This will indirectly Sum the values that reside in cells\u00a0C9:E9, which is <strong>106<\/strong> in the example below:<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/range-of-cells.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"INDIRECT Function in Excel\"  class=\"alignnone size-full wp-image-3121\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/range-of-cells.png\" alt=\"INDIRECT Function in Excel\" width=\"530\" height=\"319\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/range-of-cells.png 530w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/range-of-cells-300x181.png 300w\" sizes=\"(max-width: 530px) 100vw, 530px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<div style=\"text-align: center;\">\n<h2 style=\"text-align: center;\"><span class=\"ez-toc-section\" id=\"REFERENCE_IS_A_NAMED_RANGE\"><\/span><strong><span style=\"color: #ff0000;\">REFERENCE IS A NAMED RANGE\u00a0<\/span><\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<\/div>\n<div style=\"text-align: center;\">\n<h2 style=\"text-align: center;\">=SUM(INDIRECT(<span style=\"color: #0000ff;\">NamedRange<\/span>))<\/h2>\n<\/div>\n<p>&nbsp;<\/p>\n<p>When the referenced range is Named Range,\u00a0the INDIRECT function will go and\u00a0return the content of the Named Range.<\/p>\n<p>We can then enter a SUM function which will total the Named Range.<\/p>\n<p>We need to create a <strong>Named Range<\/strong> by selecting the data range and entering a name (with no spaces) in the Name Box:<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/named-range.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"INDIRECT Function in Excel\"  class=\"alignnone size-full wp-image-3122\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/named-range.png\" alt=\"INDIRECT Function in Excel\" width=\"347\" height=\"341\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/named-range.png 347w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/named-range-300x295.png 300w\" sizes=\"(max-width: 347px) 100vw, 347px\" \/><\/a><\/p>\n<p>We then\u00a0enter in cell <strong>K9<\/strong> the following Named Range:\u00a0<em><strong>TABLE<\/strong><\/em><\/p>\n<p>In another cell we enter <strong>=INDIRECT(K9)<\/strong><\/p>\n<p>This will indirectly Sum the values that reside in the Named Range TABLE, which is <strong>1,007<\/strong> in our example below:<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/named-range-sum.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"INDIRECT Function in Excel\"  class=\"alignnone size-full wp-image-3130\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/named-range-sum.png\" alt=\"INDIRECT Function in Excel\" width=\"542\" height=\"344\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/named-range-sum.png 542w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/named-range-sum-300x190.png 300w\" sizes=\"(max-width: 542px) 100vw, 542px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>Imagine having several Named Ranges that reference different data sets within a Workbook &amp; adding a drop down menu to show the different Named Ranges.<\/p>\n<p>Using this technique you can pick &amp; choose the different data sets and with the INDIRECT function return the summation of each, thus creating an interactive Dashboard!<\/p>\n<p>The possibilities are endless and in the next tutorials I will expand on such techniques.<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>What does it do? Returns a reference to a range. The referenced range can be a cell, a range of cells, or a named range. Formula breakdown: =INDIRECT(ref_text, [a1]) What it means: =INDIRECT(Return the referenced range, Omit if the reference is an A1 style or enter FALSE if it is a R1C1 style) The INDIRECT [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":17502,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"cos_headline_score":0,"cos_seo_score":0,"cos_headline_text":"","cos_headline_has_been_analyzed":false,"cos_last_analyzed_headline":[],"om_disable_all_campaigns":false},"categories":[11,3,279],"tags":[43,79],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/3116"}],"collection":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/comments?post=3116"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/3116\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/17502"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=3116"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=3116"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=3116"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}