{"id":1626,"date":"2020-07-20T15:04:05","date_gmt":"2020-07-20T20:04:05","guid":{"rendered":"http:\/\/xlfxs.com\/?p=1626"},"modified":"2020-07-20T15:04:05","modified_gmt":"2020-07-20T20:04:05","slug":"golast-to-jump-to-last-cell-in-a-column","status":"publish","type":"post","link":"https:\/\/xlfxs.com\/?p=1626","title":{"rendered":"GoLast to jump to last cell in a column"},"content":{"rendered":"\n

Edit 2021-07-12<\/strong>: I needed this formula to work in a worksheet having space in its name, so original one did not work, here is the new version:<\/p>\n\n\n\n

=HYPERLINK(\"[\"&MID(CELL(\"filename\",A1),SEARCH(\"[\",CELL(\"filename\",A1))+1,SEARCH(\"]\",CELL(\"filename\",A1))-SEARCH(\"[\",CELL(\"filename\",A1))-1)&\"]'\"&MID(CELL(\"filename\",A1),SEARCH(\"]\",CELL(\"filename\",A1))+1,500)&\"'!B\"&COUNT(B:B)+6,\"Go Last\")<\/code><\/p>\n\n\n\n

Again, above formula works in a worksheet with space in its name (and any other character), it already works in a workbook with space in its name.
End of Edit 2021-07-12<\/strong>:<\/p>\n\n\n\n

<\/p>\n\n\n\n

This dynamic Hyperlink cell with 1 complete formula will let you Go to first available cell in a column. To allow you to enter next row data.
It can help you especially when you have long ones.<\/p>\n\n\n\n

=HYPERLINK(\"[\"&MID(CELL(\"filename\",A1),SEARCH(\"[\",CELL(\"filename\",A1))+1,SEARCH(\"]\",CELL(\"filename\",A1))-SEARCH(\"[\",CELL(\"filename\",A1))-1)&\"]\"&MID(CELL(\"filename\",A1),SEARCH(\"]\",CELL(\"filename\",A1))+1,500)&\"!B<\/span>\"&COUNT(<\/span>B:B<\/span>)+6<\/span>,\"Go Last\")<\/code><\/p>\n\n\n\n

Above formula will create a hyperlink to go to next available row using column B<\/span>, assuming column B<\/span> has only numbers.<\/p>\n\n\n\n

The COUNT() function is used to calculate how many numbers in column B, then adds 1 to it, then select it.<\/p>\n\n\n\n

If you want to change the column, modify the B<\/span> in three places, to use any other column.<\/p>\n\n\n\n

If you have that column (column B in our formula) with text instead of numbers, use COUNTA() instead of COUNT().<\/p>\n\n\n\n

The +6<\/span> in our formula is because my sheet starts from row 5, and I wanted to shift the selection by 6 rows.<\/p>\n","protected":false},"excerpt":{"rendered":"

Edit 2021-07-12: I needed this formula to work in a worksheet having space in its name, so original one did not work, here is the new version: =HYPERLINK(“[“&MID(CELL(“filename”,A1),SEARCH(“[“,CELL(“filename”,A1))+1,SEARCH(“]”,CELL(“filename”,A1))-SEARCH(“[“,CELL(“filename”,A1))-1)&”]'”&MID(CELL(“filename”,A1),SEARCH(“]”,CELL(“filename”,A1))+1,500)&”‘!B”&COUNT(B:B)+6,”Go Last”) Again, above formula works in a worksheet with space in its name (and any other character), it already works in a workbook with space in its…<\/p>\n

Read More “GoLast to jump to last cell in a column”<\/span> »<\/a><\/p>\n","protected":false},"author":1,"featured_media":1627,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[70,74,3,79,31,36,6],"tags":[138,139,145,153],"class_list":["post-1626","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-cell","category-count-math-and-trig","category-formulas","category-hyperlink","category-information","category-lookup_and_references","category-standard_functions","tag-go","tag-golast","tag-hyperlink","tag-last"],"_links":{"self":[{"href":"https:\/\/xlfxs.com\/index.php?rest_route=\/wp\/v2\/posts\/1626","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=1626"}],"version-history":[{"count":0,"href":"https:\/\/xlfxs.com\/index.php?rest_route=\/wp\/v2\/posts\/1626\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/xlfxs.com\/index.php?rest_route=\/wp\/v2\/media\/1627"}],"wp:attachment":[{"href":"https:\/\/xlfxs.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1626"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/xlfxs.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1626"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/xlfxs.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1626"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}