HYPERLINK Wonkiness with Dynamic Ranges

Hello all- so i have been trying to get this to work for quite a while and think this may be a bug... So essentially, I am trying to use the HYPERLINK function to link to various other sheets in a wo...