Hi,
So I have a worksheet where I've set things up with 3 formulae in Row 1. One formula is actually an anchor that takes you to cell A3.
The formula in cell B1 takes you to 4 rows after the last row with data in it:
=HYPERLINK("#"&CELL("address",OFFSET(INDEX(H:H,MATCH(C1,H:H,0)),0,-3)),"Jump to the data cell")
What this formula is doing in looking for the value of C1, which in this case is just 5 spaces: " "
It's looking for these spaces in a row that his this formula in column H:
=IFNA(VLOOKUP(E365,Hidden!A:M,11,0),IF(D365="No match"," ",IF(NOT(OFFSET(H365,-4,0)<>"")," ","")))
If column E does not have the "No match" value AND column E has no value than column H has the " " value, in which case when I clicked on the hyperlink in B1 it could take me to 3 rows below the last row that had no value in D and E, if this makes sense.
This formula was working fine but recently it's been behaving weird. Which I click on the hyperlink in B1 it selects a bunch of cells in column B and it doesn't take me to the bottom of the sheet.
So I have a worksheet where I've set things up with 3 formulae in Row 1. One formula is actually an anchor that takes you to cell A3.
The formula in cell B1 takes you to 4 rows after the last row with data in it:
=HYPERLINK("#"&CELL("address",OFFSET(INDEX(H:H,MATCH(C1,H:H,0)),0,-3)),"Jump to the data cell")
What this formula is doing in looking for the value of C1, which in this case is just 5 spaces: " "
It's looking for these spaces in a row that his this formula in column H:
=IFNA(VLOOKUP(E365,Hidden!A:M,11,0),IF(D365="No match"," ",IF(NOT(OFFSET(H365,-4,0)<>"")," ","")))
If column E does not have the "No match" value AND column E has no value than column H has the " " value, in which case when I clicked on the hyperlink in B1 it could take me to 3 rows below the last row that had no value in D and E, if this makes sense.
This formula was working fine but recently it's been behaving weird. Which I click on the hyperlink in B1 it selects a bunch of cells in column B and it doesn't take me to the bottom of the sheet.
