+ Reply to Thread
Results 1 to 11 of 11

Hyperlink Formula Cell reference change Automatically

  1. #1
    Registered User
    Join Date
    08-06-2016
    Location
    Oklahoma city, Oklahoma, USA
    MS-Off Ver
    2016
    Posts
    30

    Hyperlink Formula Cell reference change Automatically

    Hello,

    So this is my formula I have for my worksheet:

    =IF(I13>0,HYPERLINK("#'"&A13&"'!E13",$G$11),"")

    I would like to get excel to automatically change the cell reference in the part of the formula "'!E13" to change when I copy the formula to the next cell down to correspond to E14 and then when copied again down to E15 and so on.

    As of now I have to manually go into each cell and change that part of the formula.

    the idea is when I make a table on another worksheet, click on the hyperlink, it brings me to that cell specified rowed cell, rather than all hyperlinks to bring me to the same cell every time.

    thanks,
    Brent

  2. #2
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Hyperlink Formula Cell reference change Automatically

    You can use ROW() in the formula to do this.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Depending on which row number the formula is in you may need to add or subtract to suit.
    The above example would work if in row 13.

    BSB

  3. #3
    Registered User
    Join Date
    08-06-2016
    Location
    Oklahoma city, Oklahoma, USA
    MS-Off Ver
    2016
    Posts
    30

    Re: Hyperlink Formula Cell reference change Automatically

    Hey Thanks a lot BSB.

    I almost bout got it I think.

    so I have the below formula now
    Please Login or Register  to view this content.
    I have this typed into the example tab, but when I paste it into the sheet tab it replaces the ROW(E1) with ROW(#REF!) and doesnt work, I have to manually type in E1 on the sheet tab for the hyper link formula to work.

    see below my file
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    08-06-2016
    Location
    Oklahoma city, Oklahoma, USA
    MS-Off Ver
    2016
    Posts
    30

    Re: Hyperlink Formula Cell reference change Automatically

    if you curious why I am doing it like this is because, this will be a part of my macro on my work template. I have tabs where data is entered in similar to example tab at bottom. when I run my filtering macro, Id like for a hyperlink to be available so I can click on it and it brings me to the sheet and specific cell.

  5. #5
    Registered User
    Join Date
    08-06-2016
    Location
    Oklahoma city, Oklahoma, USA
    MS-Off Ver
    2016
    Posts
    30

    Re: Hyperlink Formula Cell reference change Automatically

    tried this formula but still not being referenced on the sheet tab, when I copy from example tab and paste on sheet tab
    Please Login or Register  to view this content.
    Really annoying, any Help?
    Attached Files Attached Files

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Hyperlink Formula Cell reference change Automatically

    You can use this in B2 of Sheet1:

    =IF(J2>0,HYPERLINK("#'"&A2&"'!F"&ROWS($1:1)+12,"CLICK HERE"),"")

    and then copy down. If you try to copy the formula directly from row 13 of the other sheet, then the cell references which are not anchored by the $ symbol (i.e. J13, A13 and F1) will be adjusted by Excel. The J13 and A13 terms will be okay, as they will change to J2 and A2, but the F1 term cannot become negative, which is why you get the #REF error.

    Hope this helps.

    Pete

  7. #7
    Registered User
    Join Date
    08-06-2016
    Location
    Oklahoma city, Oklahoma, USA
    MS-Off Ver
    2016
    Posts
    30

    Re: Hyperlink Formula Cell reference change Automatically

    Hey Thanks Pete,

    So is there a way to reformulate that allows me to copy the formula from the example sheet and paste to Sheet1 so it will work and not throw the #REF! error?

    Its just that my work template workbook, the data filtering table I made a macro for, will copy the formula and then paste it on the filtered table, while all the other data that is copied will be pasted as values only. So when I am looking at the filtered table and I need to look at a owner in more detail, I can click on the hyperlink to bring me to the sheet and owner name on the specified row within the sheet.

  8. #8
    Registered User
    Join Date
    08-06-2016
    Location
    Oklahoma city, Oklahoma, USA
    MS-Off Ver
    2016
    Posts
    30

    Re: Hyperlink Formula Cell reference change Automatically

    if anything else, I may just have to edit the formula in VBA after its been pasted on the filtering data table I have, so when the macro is finished, the links will work.

    Edit:
    Never mind that wont work either, dont think any of this will work, due to the fact that when I filter, the filtered table gets sorted which will throw off the hyperlink formula.
    Last edited by disepyon; 07-22-2017 at 09:39 PM.

  9. #9
    Registered User
    Join Date
    08-06-2016
    Location
    Oklahoma city, Oklahoma, USA
    MS-Off Ver
    2016
    Posts
    30

    Re: Hyperlink Formula Cell reference change Automatically

    got it figured out.

    Did a bunch of researching on the web and modified the formula I found so it can suit any sheet name.
    Please Login or Register  to view this content.
    Thanks for all the help will rep both of you for showing me the right path.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    08-06-2016
    Location
    Oklahoma city, Oklahoma, USA
    MS-Off Ver
    2016
    Posts
    30

    Re: Hyperlink Formula Cell reference change Automatically

    Thought Id post up my final revision of the formula as it previously kept going to the same owner name when the hyperlink was clicked on in the Sheet1 tab.
    Now it knows to go to the owner name with the correct data corresponding with it. Works by looking up all the vlookup/match values in the ref column and moves the cursor to the name in the corresponding column.


    Please Login or Register  to view this content.
    for those wanting to use something like this, it is setup to use what ever sheet name you have. Like it my situation, on my work template, I made a vba macro to pull data from numerous sheets and filter and sort on criteria. when looking at the filtered table, if i want to look at a owner in more detail,I can click on the hyperlinked like in the example here and it will take me directly to that owner name in what ever sheet corresponding to. only the hyperlink formula is copied as PasteAll in the filter table to be able to use the hyperlink, the rest of the data is pasted as values only.

    All right, thats all i have for this.
    Later
    Attached Files Attached Files

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Hyperlink Formula Cell reference change Automatically

    Thank you for the useful update.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

+ 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. [SOLVED] Reference row of Active Cell in Hyperlink formula
    By delaing in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-12-2015, 04:46 PM
  2. VBA code to automatically change column reference in formula
    By lullaby in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-19-2015, 05:47 AM
  3. Replies: 1
    Last Post: 02-11-2015, 01:56 PM
  4. Replies: 1
    Last Post: 11-13-2013, 02:07 AM
  5. [SOLVED] How to change reference area automatically by dragging Index Match Formula down
    By Nuada in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-20-2013, 01:19 PM
  6. Formula To Automatically Change External Reference Based on Date
    By Hillary35 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-29-2013, 11:39 AM
  7. Dragging formula, change sheet reference, but not cell reference
    By Kalilaya0419 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-13-2013, 04:50 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