[\/et_pb_text][\/et_pb_column][\/et_pb_row][et_pb_row _builder_version=”4.27.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_column type=”4_4″ _builder_version=”4.27.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_text admin_label=”Connections T – Automate beyond importrange – Horizontal” module_class=”sheetgo-post-no-shadow-img md2-contained-button-light vertical-banner-container” _builder_version=”4.27.2″ _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” saved_tabs=”all” global_colors_info=”{}” global_module=”50462″ theme_builder_area=”post_content”]![\"\"](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/Add-on-sm_sheets-connected-new-connection.webp\")
<\/p>\n
![\"39\"](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/Recommended-for-Google-Workspace-badge.webp\"){kind=icon}
<\/p>\n
Automate data transfers beyond Importrange<\/span><\/p>\n Find out how<\/a>[\/et_pb_text][\/et_pb_column][\/et_pb_row][et_pb_row admin_label=”row” _builder_version=”4.16″ background_size=”initial” background_position=”top_left” background_repeat=”repeat” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_column type=”4_4″ _builder_version=”4.16″ custom_padding=”|||” global_colors_info=”{}” custom_padding__hover=”|||” theme_builder_area=”post_content”][et_pb_text _builder_version=”4.27.2″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n ROW([cell_reference])<\/strong><\/span><\/p>\n cell_reference<\/strong><\/span>\u00a0<\/span>\u2013 is the address reference to the cell whose row number we need. This is an optional parameter. So, if not specified, the ROW formula considers the address of the cell where we keyed in the formula.<\/p>\n Please note that if the\u00a0<\/span>cell_reference<\/strong><\/span>\u00a0<\/span>parameter is not a single cell, but is a range of multiple cells, the formula returns only the row number of the first row within the\u00a0<\/span>cell_reference<\/strong><\/span>.<\/p>\n Let us get our understanding of the formula more concrete by means of a few examples. Please see the snapshot below.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/06\/ROW-FORMULA-GOOGLE-SHEETS-1.png” alt=”ROW FORMULA GOOGLE SHEETS 1″ title_text=”ROW FORMULA GOOGLE SHEETS 1″ align=”center” module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.21.2″ width=”90%” max_width=”640px” 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 first example took A2 for\u00a0<\/span>cell_reference<\/strong><\/span>\u00a0<\/span>and accordingly returned 2 as output. Similarly, for A6, it returned 6 as output for the second example. The third case doesn\u2019t have any input for the\u00a0<\/span>cell_reference<\/strong><\/span>, hence it returned the row number of the cell in which we keyed in the formula.<\/p>\n In the fifth example, we provided an address to a range of cells as opposed to a single cell. And, it returned the number of the first row within the range A2:A6, which is 2.<\/p>\n It is interesting to note that we provided a named range reference as input for the\u00a0<\/span>cell_reference<\/strong><\/span>\u00a0<\/span>parameter in the last example case. As shown on the extreme right of the above image, the \u201cAges\u201d range spans from B1 to B6. Therefore, the result is the first row number of range B1:B6.<\/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 useful ways we can use the ROW formula is when we use it in conjunction with the\u00a0<\/span>ARRAYFORMULA<\/a>. We use the following method to have Google Sheets fill up series of numbers for us without having to enter them ourselves.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/06\/ROW-FORMULA-GOOGLE-SHEETS-2.png” alt=”ROW FORMULA GOOGLE SHEETS 2″ title_text=”ROW FORMULA GOOGLE SHEETS 2″ align=”center” module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.21.2″ width=”90%” max_width=”640px” 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 utility of the formula is very handy indeed. Even if we try and insert a row anywhere between the existing rows, the numbering automatically is taken care of. We will insert a row between rows 6 and 7 as shown below. And we will notice we don\u2019t have to do anything to adjust the numbering \u2013 Google Sheets does that for us automatically.<\/span><\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/10\/frame-generic-6.png” alt=”ROW Formula: Inserting a Number ” title_text=”insert-a-number” align=”center” module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.21.2″ width=”60%” max_width=”640px” 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 But then, there is a small problem. What if we try inserting a row above the cell A1? Doing so would shift the cell down and accordingly adjusts the formula as shown below, which is probably undesirable.<\/span><\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/10\/frame-generic-7.png” alt=”ROW Formula: Inserting a Row” title_text=”insert-a-row” align=”center” module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.16″ width=”60%” max_width=”640px” 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 We can fix this issue using the\u00a0<\/span>INDIRECT<\/a>\u00a0formula as shown below. Even if we insert a row above, it doesn\u2019t affect the numbering. However, the address reference 1:12 has now become static. So, inserting rows between 1 through 12 will not adjust the reference to 1:13 anymore (like it did in the third snapshot).<\/span><\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/10\/frame-generic-8.png” alt=”ROW Formula: Using Indirect Formula” title_text=”indirect-formula” align=”center” module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.16″ width=”60%” max_width=”640px” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n","protected":false},"excerpt":{"rendered":" The\u00a0ROW\u00a0formula is one of the lookup functions available within Google Sheets. It gives us the row number where the specified cell or a range of cells are located. Automate data transfers beyond Importrange Find out howSyntax ROW([cell_reference]) cell_reference\u00a0\u2013 is the address reference to the cell whose row number we need. This is an optional parameter. […]<\/p>\n","protected":false},"author":40,"featured_media":4379,"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 ROW<\/a> formula is one of the lookup functions available within Google Sheets. It gives us the row number where the specified cell or a range of cells are located.\nSyntax<\/h2>\n
Usage: ROW Formula<\/h3>\n
Use case: Auto-filling numbers<\/h5>\n
Syntax<\/h3>\nROW([cell_reference])<\/strong><\/span>\n\ncell_reference<\/strong><\/span> - is the address reference to the cell whose row number we need. This is an optional parameter. So, if not specified, the ROW formula considers the address of the cell where we keyed in the formula.\n\nPlease note that if the cell_reference<\/strong><\/span> parameter is not a single cell, but is a range of multiple cells, the formula returns only the row number of the first row within the cell_reference<\/strong><\/span>.\n
Usage: ROW Formula<\/h3>\nLet us get our understanding of the formula more concrete by means of a few examples. Please see the snapshot below.\n\n
![\"ROW](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/10\/frame-generic-4.png\")
\n\nThe first example took A2 for cell_reference<\/strong><\/span> and accordingly returned 2 as output. Similarly, for A6, it returned 6 as output for the second example. The third case doesn't have any input for the cell_reference<\/strong><\/span>, hence it returned the row number of the cell in which we keyed in the formula.\n\nIn the fifth example, we provided an address to a range of cells as opposed to a single cell. And, it returned the number of the first row within the range A2:A6, which is 2.\n\nIt is interesting to note that we provided a named range reference as input for the cell_reference<\/strong><\/span> parameter in the last example case. As shown on the extreme right of the above image, the \"Ages\" range spans from B1 to B6. Therefore, the result is the first row number of range B1:B6.\nUse case: Auto-filling numbers<\/h5>\nOne of the useful ways we can use the ROW formula is when we use it in conjunction with the ARRAYFORMULA<\/a>. We use the following method to have Google Sheets fill up series of numbers for us without having to enter them ourselves.\n\n
![\"ROW](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/10\/frame-generic-5.png\")
\n\nThis utility of the formula is very handy indeed. Even if we try and insert a row anywhere between the existing rows, the numbering automatically is taken care of. We will insert a row between rows 6 and 7 as shown below. And we will notice we don't have to do anything to adjust the numbering - Google Sheets does that for us automatically.\n\n![\"ROW](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/10\/frame-generic-6.png\")
\n\nBut then, there is a small problem. What if we try inserting a row above the cell A1? Doing so would shift the cell down and accordingly adjusts the formula as shown below, which is probably undesirable.\n\n![\"ROW](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/10\/frame-generic-7.png\")
\n\nWe can fix this issue using the INDIRECT<\/a> formula as shown below. Even if we insert a row above, it doesn't affect the numbering. However, the address reference 1:12 has now become static. So, inserting rows between 1 through 12 will not adjust the reference to 1:13 anymore (like it did in the third snapshot).\n\n![\"ROW](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/10\/frame-generic-8.png\")
\n\n ","_et_gb_content_width":"","footnotes":""},"categories":[54],"tags":[39,28],"class_list":["post-4242","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-google-sheets-formulas","tag-connections-t","tag-spreadsheets"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/posts\/4242","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/users\/40"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/comments?post=4242"}],"version-history":[{"count":0,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/posts\/4242\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/media\/4379"}],"wp:attachment":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/media?parent=4242"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/categories?post=4242"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/tags?post=4242"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}