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!

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.

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

Thanks guys

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?

Seriously, that has warped my mind with possibilities!!

Thanks again mate :-)