Batch Hyperlinks To Invoices In Other Folder

[Downunder2012]

Hi All
I have just today subscribed to the forum, I am very rusty on Excel having not used it for quite sometime I did an intermediate course in Excel XP and gained a certificate but that is not helping me much with my Excel 2007. I will be enrolling a new course later but as it is getting very close to tax deadline I would greatly appreciate any help in my exercise. I have in column A of my worksheet a list of consecutive invoice numbers starting at GLA000249 going to GLA000323 I want to be able to use a macro or a formula to link those invoice numbers to the actual invoices in a folder in "my documents\my excel files\GLA000249.xlsx etc etc" I have tried the macro below but receive error message "error opening file"

Please Login or Register  to view this content.

[FDibbins]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

i have added them for you this time, but please remember in the future

[FDibbins]

try using the indirect() function?

=hyperlink(indirect("your-path"&A1&.xlsx"))

[Downunder2012]

Hi thanks for the rapid response here is my formula with the first invoice number at cell ref A6 =HYPERLINK(INDIRECT("my documents\my excel files\"&A6&"GLA000249.xlsx")) I am getting a circular reference now #REF

[FDibbins]

are you entering that IN A6? f so, that will give you a circ ref, because the formula references A6...so it is referencing itself

[Downunder2012]

Yes, I think the penny just dropped I have to enter the formula in a separate cell?

[FDibbins]

yup, else it is referring to itself

[Downunder2012]

I really appreciate this but put formula in another cell and still receive error, I have to go to work now so I won't be here for an immediate reply but thanks again you have been very helpful I'll check tomorrow.