The difference between the INDIRECT function and a typical direct function is that a typical function directly references a cell (or range of cells) within the formula. In contrast, the INDIRECT function uses the cell address (in the form of a text string) to find the relevant cell.<\/p>\n
Note: If you want to learn more about other google sheet functions, we have a post with 100+ Google Sheet Functions<\/a><\/p>\n Using a text string to reference a cell provides several advantages that are outlined below.<\/p>\n INDIRECT(cell_reference_as_string, [is_A1_notation])<\/strong><\/span><\/p>\n cell_reference_as_string<\/strong><\/span>\u00a0<\/span>\u2013 is the text form of a cell address. Notice that it is not the cell reference itself.\u00a0Because it is in the form of a text string, it should be enclosed in double quotation marks unless it is referencing a cell that holds the text string (see the examples below).<\/p>\n is_a1_notation<\/strong><\/span>\u00a0<\/span>\u2013\u00a0this is an optional input that tells the function what type of notation the cell address is in. There are two kinds of representations for a cell address. A1 notation specifies the cell by the column letter (A, B, C, etc.) and the row number (1, 2, 3, etc.), identifying cells in the form of A1, B3, F14, and so on. Another way of representing a cell is R1C1, which gives the row number (R1) and the column number (C1). In this format, cell A1 would be R1C1, and cell B3 would be R3C2 (because B3 is the third row in the second column). TRUE is the value by default, and you get A1 notation in return. Otherwise, if you need R1C1 notation, you need to specify FALSE.<\/p>\n The easiest way to understand this concept is to see it in use, so I’ve provided some examples below, each using a different combination.<\/p>\n As you can see, the first parameter can be a direct string enclosed in double quotes (rows 2 and 3), a cell reference that holds the address string (row # 7), or even a concatenated string (rows 4 and 5).<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/05\/INDIRECT-FORMULA-GOOGLE-SHEETS-1.png” alt=”INDIRECT FORMULA GOOGLE SHEETS 1″ title_text=”INDIRECT FORMULA GOOGLE SHEETS 1″ align=”center” module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n To help you understand the function better, I\u2019ll go through the list of the different variations of the formula and the results that each one generates.<\/p>\n In the first formula =INDIRECT(\u201cB3\u201d), I simply specify that I want the result of the formula to be whatever is in cell B3, which is Brian<\/strong>.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/05\/INDIRECT-FORMULA-GOOGLE-SHEETS-2.png” alt=”INDIRECT FORMULA GOOGLE SHEETS 2″ title_text=”INDIRECT FORMULA GOOGLE SHEETS 2″ align=”center” module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n In the second formula =INDIRECT(\u201cB2\u201d, TRUE), I do the same thing, but this time I ask for the result to be from cell B2, which is Alex<\/strong>.<\/p>\n I also indicate TRUE<\/strong>, which means I want A1 notation.<\/p>\n Note that you don\u2019t need to specify this. The function assumes that you are using A1 notation unless you indicate otherwise (by using FALSE which indicates R1C1 notation).<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/11\/indirect-function-missing-image.png” alt=”indirect-function-example” title_text=”indirect-function-missing-image” _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n In the third formula =INDIRECT(“Sheet1!”&A4,True) I concatenate a text string that will tell the formula which cell I want to reference.<\/p>\n To do this, you start by saying \u201cSheet1!\u201d, followed by &A4, which tells the formula to look at cell A4 on Sheet 1.<\/p>\n The text in cell A4 is \u201cB7\u201d, which tells the formula to give the output from cell B7, which is Fabio<\/strong>.<\/p>\n In this formula, I also indicate TRUE<\/strong> for the A1 notation, which is again optional.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/05\/INDIRECT-FORMULA-GOOGLE-SHEETS-4.png” alt=”INDIRECT FORMULA GOOGLE SHEETS 4″ title_text=”INDIRECT FORMULA GOOGLE SHEETS 4″ align=”center” module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The fourth formula =INDIRECT(A5&”!B8″) does the same thing but in a different way.<\/p>\n It spells out \u201cSheet1!B8\u201d by referencing cell A5 (which contains \u201cSheet1\u201d) and adding the \u201c!B8\u201d.<\/p>\n As a result, the indirect formula looks at cell B8 and returns Greg<\/strong>.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/05\/INDIRECT-FORMULA-GOOGLE-SHEETS-5.png” alt=”INDIRECT FORMULA GOOGLE SHEETS 5″ title_text=”INDIRECT FORMULA GOOGLE SHEETS 5″ module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The fifth formula =INDIRECT(“R5C2”,FALSE) uses R1C1 format to identify cell B5 by saying R5C2 (the fifth row in the second column).<\/p>\n In this case, I must let the formula know that I\u2019m using R1C1 notation by adding FALSE<\/strong> at the end of the formula.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/05\/INDIRECT-FORMULA-GOOGLE-SHEETS-6.png” alt=”INDIRECT FORMULA GOOGLE SHEETS 6″ title_text=”INDIRECT FORMULA GOOGLE SHEETS 6″ module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The sixth formula =INDIRECT(A7,FALSE) references another cell, A7, which points the formula to cell B6 in R1C1 format, so again I add FALSE.<\/p>\n Notice that in this example A7 is not in quotation marks because it is not a text string but a reference to the A7 cell that holds the text string (R6C2).<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/05\/INDIRECT-FORMULA-GOOGLE-SHEETS-7.png” alt=”INDIRECT FORMULA GOOGLE SHEETS 7″ title_text=”INDIRECT FORMULA GOOGLE SHEETS 7″ module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The last formula =INDIRECT(SUBSTITUTE(SUBSTITUTE(A8,”[“,””),”]”,””),FALSE) uses the SUBSTITUTE formula to change the R[1]C[1] cell address format to R1C1, since this is the notation that the formula can handle.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/05\/INDIRECT-FORMULA-GOOGLE-SHEETS-8.png” alt=”INDIRECT FORMULA GOOGLE SHEETS 8″ title_text=”INDIRECT FORMULA GOOGLE SHEETS 8″ module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text admin_label=”Connections T – One tool – Horizontal” module_class=”sheetgo-post-no-shadow-img md2-contained-button vertical-banner-container” _builder_version=”4.27.4″ _module_preset=”default” background_color=”#f2f7ff” max_width=”700px” module_alignment=”center” max_height=”300px” custom_margin=”20px|0px|20px|0px|true|true” custom_padding=”25px|25px|25px|25px|true|true” sticky_limit_bottom=”section” border_radii=”on|20px|20px|20px|20px” border_width_all=”1px” border_color_all=”#d9e7ff” global_colors_info=”{}” global_module=”255434″ theme_builder_area=”post_content”]<\/p>\n One tool to merge, split, and filter all your spreadsheet data<\/span><\/p>\n Find out how<\/a><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n One of the benefits of using the INDIRECT function is that by specifying the cell(s) you want the formula to reference, those cells stay the same even if you add new rows or columns to the sheet.<\/p>\n The example below shows the difference between using a regular SUM formula, and using the SUM formula combined with an INDIRECT formula, =SUM(INDIRECT(\u201cB2:B6\u201d))<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/05\/INDIRECT-FORMULA-GOOGLE-SHEETS-9.png” alt=”INDIRECT FORMULA GOOGLE SHEETS 9″ title_text=”INDIRECT FORMULA GOOGLE SHEETS 9″ module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Here, you can see that to add up the revenue for Monday to Friday, SUM can be used either with or without the INDIRECT function.<\/p>\n Sum function references the cells B2:B6 directly while using the INDIRECT formula references the cells as a text string \u201cB2:B6\u201d.<\/p>\n The difference comes into play when you make changes to the sheet such as by adding another row.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/05\/INDIRECT-FORMULA-GOOGLE-SHEETS-10-GOOD.png” alt=”INDIRECT FORMULA GOOGLE SHEETS 10 GOOD” title_text=”INDIRECT FORMULA GOOGLE SHEETS 10 GOOD” module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n In the screenshot above, you can see that an extra row has been inserted above Monday in order to add revenue from Sunday. When this happens, the SUM function automatically changes its cells to B3:B7.<\/p>\n The formula that used the INDIRECT function, however, maintained the original range of B2:B6.<\/p>\n In some situations, the fact that SUM (and other formulas) can change its range based on alterations to the rows and columns in a sheet can be helpful. On the other hand, this might be frustrating when you want to keep the range the same.<\/p>\n In these cases, the INDIRECT function is a useful way of achieving this.<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Named ranges<\/a> can be created to refer to a set group of cells. The INDIRECT formula can use a named range for a variety of calculations.<\/p>\n In the example below, I\u2019ve created a named range for cells B3:B7 called Monday_to_Friday.<\/p>\n This can be done by selecting the cells and clicking Data<\/strong> > Named Ranges<\/strong>.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/05\/INDIRECT-FORMULA-GOOGLE-SHEETS-11.png” alt=”INDIRECT FORMULA GOOGLE SHEETS 11″ title_text=”INDIRECT FORMULA GOOGLE SHEETS 11″ module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Once the named range is created, it can be referenced using the INDIRECT function.<\/p>\n In the example above, I use =SUM(INDIRECT(“Monday_to_Friday”)) to call up the Monday_to_Friday range and then output the sum of this range.<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Another use of the INDIRECT function is referencing data in other sheets. <\/span><\/p>\n In the simple example below, you can see how the function can be used to pull data from other sheets.<\/span><\/p>\n I\u2019ve created a second tab called Sheet2<\/strong>, and on cell B4 of that sheet, I\u2019ve entered “Data from Sheet 2\u201d.<\/p>\n Then on Sheet 1, I can use the INDIRECT formula to refer to that cell and output the data from that cell by saying =INDIRECT(“Sheet2!”&”B4”)<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/05\/INDIRECT-FORMULA-GOOGLE-SHEETS-12.png” alt=”INDIRECT FORMULA GOOGLE SHEETS 12″ title_text=”INDIRECT FORMULA GOOGLE SHEETS 12″ module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n This can be useful when you have a sheet with multiple tabs that you are pulling information from. Instead of including Sheet2 in the formula, you can write “Sheet2” in a cell, then have the INDIRECT formula refer to that cell.<\/p>\n If you then want to change it to get data from another tab, instead of changing the formula you can simply change the cell to say “Sheet3” instead of Sheet2, for example.<\/p>\n Likewise, you can do the same with the specific cell that you\u2019re referencing on the sheet (B4 in this example).<\/p>\n The INDIRECT function can also be used to use conditional formatting with multiple tabs.<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The INDIRECT function can be combined with the MATCH function to return a dynamic range of cells.<\/p>\n In the following example, I combine the INDIRECT and MATCH functions to give me the revenues for the days that I specify, using the formula =INDIRECT(“B” & MATCH(B10,A:A,FALSE) & “:B” & MATCH(B11,A:A,FALSE), TRUE)<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/05\/INDIRECT-FORMULA-GOOGLE-SHEETS-13.png” alt=”INDIRECT FORMULA GOOGLE SHEETS 13″ title_text=”INDIRECT FORMULA GOOGLE SHEETS 13″ module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n As you can see from the screenshot, I\u2019ve entered Monday and Thursday as the start and end days that I want to view revenue for.<\/p>\n The MATCH formula finds those days in my data range and identifies the rows that they\u2019re in. These rows are put into the INDIRECT formula as the rows between which I want to return the data. The result is that the formula gives the revenues from Monday to Thursday.<\/p>\n By setting it up this way, I can get data from different ranges of days just by changing the start and end day cells.<\/p>\n I can also combine this formula with other functions, such as SUM, to give me the overall revenue from the range of days I specify.<\/p>\n I\u2019ve done this in the example below, by typing =SUM(INDIRECT(“R” & MATCH(B10,A:A,FALSE) & “C2” & “:R” & MATCH(B11,A:A,FALSE) & “C2”, false))<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/05\/INDIRECT-FORMULA-GOOGLE-SHEETS-14.png” alt=”indirect-function-google-sheets-13″ title_text=”INDIRECT FORMULA GOOGLE SHEETS 14″ module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n As you can see, I\u2019ve used R1C1 notation in this example instead of the A1 notation of the previous example.<\/p>\n However, the logic is the same: use the MATCH function to identify the data you want to get and use the INDIRECT function to call up that data.<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The INDIRECT formula can be used to consolidate information from multiple tabs in a spreadsheet.<\/p>\n In the following example, I have a spreadsheet with sales data for the year. Each month is a separate tab, which has weekly sales data and a total sales value for the month, as shown in the following screenshot.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/05\/INDIRECT-FORMULA-GOOGLE-SHEETS-15.png” alt=”INDIRECT FORMULA GOOGLE SHEETS 15″ title_text=”INDIRECT FORMULA GOOGLE SHEETS 15″ module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n In the Summary<\/strong> tab, I want to bring in all the monthly sales totals.<\/p>\n Instead of copying and pasting each value, I can use the INDIRECT formula to access data in each tab using the following formula: =INDIRECT(A2&”!B6″)<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/05\/INDIRECT-FORMULA-GOOGLE-SHEETS-16.png” alt=”indirect-function-google-sheets-14″ title_text=”INDIRECT FORMULA GOOGLE SHEETS 16″ module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n As you can see, this formula tells the indirect function to look in each tab and return the total sales for that month, which is in cell B6<\/strong> of each tab.<\/p>\n By doing this, you can quickly consolidate data across multiple tabs. The best part is that when the numbers change in any of the tabs, that change is reflected in the summary tab automatically!<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Will the INDIRECT formula result in errors? Yes, of course!<\/p>\n The following screenshot illustrates the outcome when you try to input A1 style address notation for cell_reference_as_string and FALSE for the is_A1_notation parameter.<\/p>\n Doing this will obviously leave the INDIRECT formula looking for R1C1 style notation, and it doesn\u2019t find one. Hence the error!<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/05\/INDIRECT-FORMULA-GOOGLE-SHEETS-17.png” alt=”INDIRECT FORMULA GOOGLE SHEETS 17″ title_text=”INDIRECT FORMULA GOOGLE SHEETS 17″ module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The vice versa is also true, as indicated in the snapshot below.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/05\/INDIRECT-FORMULA-GOOGLE-SHEETS-18.png” alt=”INDIRECT FORMULA GOOGLE SHEETS 18″ title_text=”INDIRECT FORMULA GOOGLE SHEETS 18″ module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Now the worst-case scenario.<\/p>\n You try to input a text form that is neither A1 representation nor the R1C1 representation, but just some dummy text. And, of course, it should throw up an error!<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/05\/INDIRECT-FORMULA-GOOGLE-SHEETS-19.png” alt=”INDIRECT FORMULA GOOGLE SHEETS 19″ title_text=”INDIRECT FORMULA GOOGLE SHEETS 19″ module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Thanks for reading \u2014 that’s Sheetgo’s in-depth guide on how to use the INDIRECT function in Google Sheets. Check out our other articles for more spreadsheet tips!<\/span><\/p>\n Share it with your friends and colleagues via the social media buttons on the left.<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.22.2″ background_color=”#f9f9ff” custom_margin=”40px||40px||false|false” custom_padding=”15px|25px|15px|25px|true|true” border_width_left=”3px” border_color_left=”#808e95″ saved_tabs=”all” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Editor\u2019s note<\/strong>: This is a revised version of a previous post that has been updated for accuracy and comprehensiveness.<\/em><\/p>\n [\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n","protected":false},"excerpt":{"rendered":" The INDIRECT function in Google Sheets takes in the cell address in the form of text and returns a cell reference. It works in the opposite way to the ADDRESS function, which returns an address in text format. The difference between the INDIRECT function and a typical direct function is that a typical function directly […]<\/p>\n","protected":false},"author":40,"featured_media":27671,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_et_pb_use_builder":"on","_et_pb_old_content":" The INDIRECT<\/a> formula in Google Sheets takes in the cell address in the form of text and returns a cell reference. It works the opposite as that of the ADDRESS<\/a> formula, that returns an address in the text form.<\/p>\n INDIRECT(cell_reference_as_string, [is_A1_notation])<\/strong><\/span><\/p>\n cell_reference_as_string<\/strong><\/span> - is the text form of a cell address. Notice that it is not the cell reference itself.<\/p>\n is_a1_notation<\/strong><\/span> \u2013 there are two kinds of representations for a cell address. One in the form of A1. And the other way of representing the same cell is R1C1 (just a short representation for row # 1 and column # 1). TRUE is the value by default, and we get A1 notation in return. Otherwise, if we need R1C1 notation, we need to specify FALSE.<\/p>\n We understand the concept better with help of examples. So, here are a few combinations of the formula.<\/p>\n You'll notice that the first parameter can be a direct string enclosed in double quotes (rows 2 and 3), a cell reference that holds the address string (row # 7), or even a concatenated string (rows 4 and 5).<\/p>\n Please note that the INDIRECT formula cannot handle R[1]C[1] style of address notation. Instead it can process R1C1 style. So, in case we have to process the former representation, we can work around it like we did in the row # 8. We substituted the square brackets with empty strings using the SUBSTITUTE formula.<\/p>\n Will the INDIRECT formula result in errors? Yes, of course!<\/p>\n The following screenshot illustrates the outcome when we try to input A1 style address notation for cell_reference_as_string<\/strong><\/span> and indicate FALSE for the is_A1_notation<\/strong><\/span> parameter. Doing this will obviously leave the INDIRECT formula looking for R1C1 style notation, and it doesn't find one. Hence the error!<\/p>\n The vice versa is also true, as indicated in the snapshot below.<\/p>\n Now the worst case scenario. We try and input a text form that is neither A1 representation nor the R1C1 representation, but just some dummy text. And, of course it should throw up an error!<\/p>\nSyntax<\/h3>\n
How to use the INDIRECT function<\/h3>\n
Example 1<\/h4>\n
Example 2<\/h4>\n
Example 3<\/h4>\n
Example 4<\/h4>\n
Example 5<\/h4>\n
Example 6<\/h4>\n
![\"\"](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/connections-top-processors-main.webp\")
<\/p>\n![\"39\"](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/Recommended-for-Google-Workspace-badge.webp\"){kind=icon}
<\/p>\nMaintaining a cell range in a formula<\/h3>\n
Using INDIRECT with Named Ranges<\/h3>\n
How to use INDIRECT to reference other sheets<\/h3>\n
How to use the INDIRECT function with MATCH<\/h3>\n
Using INDIRECT formula with tabs<\/h3>\n
Errors \u2013 INDIRECT formula<\/h3>\n
Did you like this post?<\/h3>\n
Syntax<\/h3>\n
Usage: INDIRECT formula<\/h3>\n
![\"INDIRECT](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/Indirect1.png\")
<\/p>\n![\"INDIRECT](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/indirect2.png\")
<\/p>\n![\"\"](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/indirect3.png\")
<\/p>\n![\"INDIRECT](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/indirect4.png\")
<\/p>\n