+ Reply to Thread
Results 1 to 9 of 9

Hyperlinks to local files - Cell reference changes when copoed down the sheet.

  1. #1
    Registered User
    Join Date
    01-30-2012
    Location
    Wakefield, England
    MS-Off Ver
    Excel 2010
    Posts
    40

    Hyperlinks to local files - Cell reference changes when copoed down the sheet.

    Hi all, just wondering if I could have a little help - this is a tricky one for me.

    I have a spreadsheet list full of customer/client information regarding there estimates. In column B (B3 onwards) is there estimate number.

    In 'C:\Users\Matthew Tingle\Documents\BUSINESS DOCUMENTS\ESTIMATES\2013' I have all the estimates saved in .doc format.

    An example estimate number is 13-1492 - 13 meaning the year and the number is generated by drilling down B3+1.

    What I would like to do is create a list of hyperlinks that update automatically in regards the estimate number in column B3.

    For example cell B3 is estimate 13-1426 - I need a hyperlink to:

    C:\Users\Matthew Tingle\Documents\BUSINESS DOCUMENTS\ESTIMATES\2013\13-1426.doc

    That is easy but I need this hyperlink to copy down from cell B2-B186 without typing it in again with the last part of this local link changing - if that makes sense :-S

    All help is appreciated!

  2. #2
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,741

    Re: Hyperlinks to local files - Cell reference changes when copoed down the sheet.

    Hi,

    "An example estimate number is 13-1492 - 13 meaning the year and the number is generated by drilling down B3+1."

    Can you expound upon this part, please? If I have "13-1492" in cell B3, then =B3+1 will return a #VALUE! error since I am attempting to perform addition with a text value.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

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

    Re: Hyperlinks to local files - Cell reference changes when copoed down the sheet.

    May be try this...

    =HYPERLINK("C:\Users\Matthew Tingle\Documents\BUSINESS DOCUMENTS\ESTIMATES\2013\"&B5&".doc","Click Me")

  4. #4
    Forum Expert
    Join Date
    06-09-2010
    Location
    Australia
    MS-Off Ver
    Excel 2013
    Posts
    1,714

    Re: Hyperlinks to local files - Cell reference changes when copoed down the sheet.

    Hi
    if all the files are in the same directory you can easily do this with a macro. Try this:

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    01-30-2012
    Location
    Wakefield, England
    MS-Off Ver
    Excel 2010
    Posts
    40

    Re: Hyperlinks to local files - Cell reference changes when copoed down the sheet.

    Just to quickly expand - Column A3(onwards) houses the year number, in this instance 13. 13- is not within cell B.

    Thanks guys

  6. #6
    Registered User
    Join Date
    01-30-2012
    Location
    Wakefield, England
    MS-Off Ver
    Excel 2010
    Posts
    40

    Re: Hyperlinks to local files - Cell reference changes when copoed down the sheet.

    Thanks SixthSence I've got it!

    =HYPERLINK("C:\Users\Matthew Tingle\Documents\BUSINESS DOCUMENTS\ESTIMATES\2013\13-"&B15&".docx","Click Me")

    I just added the '13-' before the "&B15&".

    To be honest its the syntax of "& &" that i needed to know, that is such a big help for other projects as well so thank you!

    Would this syntax work with a IF or IF AND statement?

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

    Re: Hyperlinks to local files - Cell reference changes when copoed down the sheet.

    Glad it helps you and thanks for the feedback

    & used as a joiner.... Yes it can be used with any functions and even with VBA code too...

  8. #8
    Registered User
    Join Date
    01-30-2012
    Location
    Wakefield, England
    MS-Off Ver
    Excel 2010
    Posts
    40

    Re: Hyperlinks to local files - Cell reference changes when copoed down the sheet.

    Seriously, that has warped my mind with possibilities!!

    Thanks again mate :-)

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

    Re: Hyperlinks to local files - Cell reference changes when copoed down the sheet.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ 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. Changing sheet reference on multiple hyperlinks
    By Dobo in forum Excel General
    Replies: 2
    Last Post: 07-18-2013, 09:04 PM
  2. Find Hyperlinks, Copy Hyperlinks to alternative sheet, print all hyperlinks
    By matrixpom in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-13-2013, 05:13 PM
  3. VBA macro to autofill Incrementing Hyperlinks to Local Sheets
    By freud1 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-27-2009, 10:17 AM
  4. Hyperlinks - Move file to local drive, all links break
    By Shawn McGowen in forum Excel General
    Replies: 1
    Last Post: 03-08-2006, 08:52 PM
  5. [SOLVED] local support files
    By DKS in forum Excel General
    Replies: 1
    Last Post: 09-04-2005, 08:05 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