I am desperately looking for help, oh god, please some help. I literally want to pull my own teeth and hair out right about now!

=HYPERLINK("[C:\Users\DT168\Desktop\20161122.xlsx]overtime!B4","CLICK HERE")

I am using this formula to hyperlink to another cell in another work book, what I would really like to do is make it so that i can then drag the original cell down to auto fill in correlation with the target cell. Ie. when I drag the cell down in the workbook I am using the HYPERLINK formula in the 'B4' in the above formula would change to 'B5', 'B6', 'B7' and so on. At the moment it will just copy the formula down leaving 'B4' in.

PLEASE FOR THE LOVE OF GOD COULD SOMEONE HELP? I have found numerous mindboggling formula that look like it should work such as:

=HYPERLINK("#"&CELL("address",INDIRECT("Sheet1!D"&ROW())), D1) BUT I CAN'T MAKE IT WORK!

PLEASE HELP!