+ Reply to Thread
Results 1 to 15 of 15

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

Hybrid View

  1. #1
    Registered User
    Join Date
    01-26-2014
    Location
    canada
    MS-Off Ver
    Excel 2010
    Posts
    8

    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!!!

  2. #2
    Registered User
    Join Date
    01-26-2014
    Location
    canada
    MS-Off Ver
    Excel 2010
    Posts
    8

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

    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 #)

  3. #3
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

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

    Just confirm me why you are quoting the below formula withing DOUBLE quotes?

    "#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))"


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  4. #4
    Registered User
    Join Date
    01-26-2014
    Location
    canada
    MS-Off Ver
    Excel 2010
    Posts
    8

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

    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

  5. #5
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

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

    # refers to ThisWorkbook. So when you use # in hyperlink formula then it check for the sheet within the current workbook

  6. #6
    Registered User
    Join Date
    01-26-2014
    Location
    canada
    MS-Off Ver
    Excel 2010
    Posts
    8

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

    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

  7. #7
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

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

    What about this?

    =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")

  8. #8
    Registered User
    Join Date
    01-26-2014
    Location
    canada
    MS-Off Ver
    Excel 2010
    Posts
    8

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

    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!
    Attached Files Attached Files

  9. #9
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

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

    In N6 Cell - Array Formula - Requires CTRL+SHIFT+ENTER

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

    Array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

  10. #10
    Registered User
    Join Date
    01-26-2014
    Location
    canada
    MS-Off Ver
    Excel 2010
    Posts
    8

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

    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 -_-

  11. #11
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

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

    Quote Originally Posted by fcbtim View Post
    the hyperlink would actually be displayed as a bunch of numbers with a hyperlink in the background
    I am unable to follow the above quoted lines

    Can you please explain it in detail for getting better understanding?

  12. #12
    Registered User
    Join Date
    01-26-2014
    Location
    canada
    MS-Off Ver
    Excel 2010
    Posts
    8

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

    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.

  13. #13
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

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

    Ok... thanks for the brief

    Can you please let us know where the original location is present in your database?

    Can we match the friendly name with the list and get the original location?

    12345678 is a identifier to find the Location or 12345678 is an location?

  14. #14
    Registered User
    Join Date
    01-26-2014
    Location
    canada
    MS-Off Ver
    Excel 2010
    Posts
    8

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

    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!!!!!!

  15. #15
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

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

    Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to drag a formula while skipping every other cell reference
    By sbham in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-13-2013, 06:17 AM
  2. Drag formula down and skip 2 cell references instead of 1 cell reference
    By cat2005 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 07-09-2013, 11:45 AM
  3. Drag a formula down one cell, reference cell jumps down four cells?
    By rrbest in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-13-2013, 10:59 AM
  4. Drag a formula right one cell, reference cell jumps right 2 cells?
    By mukulhanda in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-13-2013, 12:10 AM
  5. Replies: 3
    Last Post: 10-04-2012, 02:49 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1