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 name.
End of Edit 2021-07-12:
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.
=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")
Above formula will create a hyperlink to go to next available row using column B, assuming column B has only numbers.
The COUNT() function is used to calculate how many numbers in column B, then adds 1 to it, then select it.
If you want to change the column, modify the B in three places, to use any other column.
If you have that column (column B in our formula) with text instead of numbers, use COUNTA() instead of COUNT().
The +6 in our formula is because my sheet starts from row 5, and I wanted to shift the selection by 6 rows.
