trying to drag down a formula, but the cell reference # not updating

hi all,

first post here. im not very proficient with excel but everyones gotta start somewhere!
my problem is sort of two-fold, but ill explain the main dragging down issue first.

im having a problem with trying to drag down a formula (see below). the cell reference A6 and E6 is not updating to A7 and E7 etc etc when i drag the formula down using the corner thing. it remains constant at A6 and E6.

=HYPERLINK("#INDEX('Shipping Tracker Week X'!$F$4:$F$3000,MATCH(1,('Shipping Tracker Week X'!$B$4:$B$3000=A6)*('Shipping Tracker Week X'!$D$4:$D$3000=E6),0))","Link 1")

im pretty sure the problem comes down to the hashtag and quotation marks that i added, as the formula properly increased the cell references when i drag down without the hashtag and quotations. however, i sort of need it because adding those seems to be the only way i can get the hyperlink to link me over to the other sheet and to the correct cell containing the hyperlink.

part 2 of problem:
the hashtag and quotations is currently a workaround method for me. the original intention was for the hyperlink to directly link me to a website, which is referenced from a separate sheet. however, without the hashtag and quotations, i get a "Cannot open specified file" error. not really sure how to solve this issue, and it's been giving me a headache!

hopefully someone can help me out. thank you!!!

i'd also like to clarify a bit on what the formula is.

i am trying to match a store # and project # to another worksheet containing a hyperlink to a website. i had to sift through google to finally find a formula that would work for me. basically F4:F3000 is just a bunch of hyperlinks to websites, and im trying to pull up a hyperlink by matching from B4:B3000=A6 (store #) and D4:D3000=E6 (project #)

thanks for the reply! i wish i could say, but it was literally just a workaround i found off google that seemed to work for me. im not sure exactly what the "# bla bla" is actually doing. :S

i see. that explains a lot hahaaha. without it though, i get a "Cannot Open Specified File" error.
adding the hash and quotations is currently the only way i can SORT OF get the hyperlink to work because at least it's leading me to the correct cell.

i just need to figure out how to drag down so the cell references also update to A7, A8, A9, etc etc.

EDIT: or if i can just get the hyperlink to work... that would be even better! haha :D

hmm, i get a "Reference is not valid error." lemme see if i can get you an example file for you to see.

see attached!

thanks a ton!

thank you! one step closer, but i forgot to approximate the real life scenario. the hyperlink would actually be displayed as a bunch of numbers with a hyperlink in the background and not an actual web address like "www.blabla.bla". my bad -_-

sorry, i was very unclear.

the hyperlink would not be "www.google.ca". it would be, for example, listed under a "friendly" name "Google". so in my scenario, for example, the hyperlink would be a number "12345678" and the actual hyperlink itself would not be displayed.

I'm not sure what you mean by location. Essentially, the hyperlink is a tracking PIN number. That PIN number can be 1234678, a unique number, that links to the purolator website for me to check on the status of a particular delivery. Each PIN number is unique and the way I thought I could hyperlink to each unique PIN number (from the shipping tracker week x tab)is by matching a particular store # and project # as per my first tab in my workbook.

Hopefully that sheds a bit more light? Thanks!!!!!!