Dynamic Hyperlinking!

The UK's most regular instructor-led training courses.
Training information: excel consultant · Microsoft+excel+training · Microsoft Excel Training London
See also · excel-courses-london · excel courses in london · excel microsoft training

Resolved · High Priority · Version 2003

28 posts

ReplyFri 19 Jun 2009, 08:09Delegate Gareth said...

Gareth has attended:
Excel VBA Intro Intermediate course
Excel Intermediate course
Excel Advanced course

Dynamic Hyperlinking!

Hey,

I want to insert a hyperlink that takes a value from a column to help search and find the matching file in the destination folder.

For example in column D I have a client number along the lines of 56/GH/8976/098. I want Excel to take that value of column D, then link in to the file with the same name in the folder I want the hyperlink to go to.

I can do straight forward hyperlinks to a document that is fixed but I cant do one that will look up different names.

Any help with this much appreciated.

Thanks in advance.

Version	Date	Location	Places available	Book	Next place rate (£)
Excel Intermediate 1 day course
Version	Date	Location	Places available	Book	Pay by Card	Pay by Invoice
2007	Thu 18 Mar 2010	Bloomsbury	1		£224	£225
2003	Thu 18 Mar 2010	Limehouse	0	FULL
2007	Mon 22 Mar 2010	Limehouse	4		£224	£225
2003	Tue 23 Mar 2010	Southwark	7		£193	£198
2007	Thu 25 Mar 2010	Rochester (Hoo)	0	FULL
2003	Fri 26 Mar 2010	Limehouse	4		£224	£225
Full Schedule: See all 79 Excel Intermediate course dates. Bookings currently available until 23rd December 2010.

633 posts

ReplyFri 19 Jun 2009, 16:03Trainer Simon said...

RE: Dynamic Hyperlinking!

Hi Gareth,

Thank you for your question.

I have come up with a possible solution which I will outline below:

=HYPERLINK(CONCATENATE("C:\",D1,".xls"),D1)

This function will combine the location of the file with the value specified in the cell and file type and finally the value from the cell displayed as the hyperlink name.

As you are trying to link the client code with the filename, the client code cannot contain a / or a \ as excel files cannot be saved with a / or \ in the filename.

In a blank cell next to the client number, enter the formula above but change the cell reference and filename as appropriate. Copy and paste the formula where appropriate.

I hope this answers your question.

Regards

Simon

Fri 26 Jun 2009: Automatically marked as resolved.

· How Many Different Ways Can You Sum In Excel 2007?
· What's In A Name?
· Excel in the Home Office
· The Pursuit of Excellence
· From the Classroom to the Corporate World

Please browse our web site to find out more about
excel consultant and other Microsoft training courses.