excel linking workbooks hyperlin

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Excel Linking Workbooks with Hyperlink Formula! | Excel forum

Excel Linking Workbooks with Hyperlink Formula! | Excel forum

resolvedResolved · High Priority · Version 2003

Dee has attended:
Access Intermediate course
Excel Advanced course
Visio Advanced course

Excel Linking Workbooks with Hyperlink Formula!

I have 2 workbooks, Workbook 1 contains a spreadsheet with data and Workbook 2 contains 2 spreadsheets, one called Location and the other called Payable. Workbook 1 cannot be accessed by the users using Workbook 2.

In the Location sheet of Workbook 2 there are 2 columns, Project No. and Location. The location column cells display the file path of Workbook 1 in text form.

I want to create a formula in the Payable sheet of Workbook 2 to display a hyperlink using the cell entry under the Location column in the Project sheet and a defined name located in Workbook 1

RE: Excel Linking Workbooks with Hyperlink Formula!

Hi Dee

Thank you for your post.

Try the following:

1. To create a link between the Location and Payable sheets for the hyperlink, select the cell in the Payable sheet where you want the hyperlink to appear.
2. Type in = then go to the Location sheet and select the cell containing the file path then press Enter.

The file path will not display as a hyperlink at this stage.

To do this:
1. Click on the cell in the Payable sheet where you have just created the link.
2. Edit the formula in this cell as follows:
=hyperlink(cell reference from Location sheet,"friendly name")

where friendly name displays the text you want to appear in the cell. The text must be surrounded by double quotation marks.

This will simply create a link to Workbook 1. If you wish to create a hyperlink to a specific cell or named range in that workbook this will be more complicated because you have created a link between the Payable and Location sheets to display the link to Workbook 1 rather than entering the hyperlink directly into the cell on the Payable sheet.

All I can suggest is playing around with the file path that you have entered into the Location sheet to see if you can get it to reference a particular cell in Workbook 1.

If you look up the hyperlink function in Excel this may shed some light on this.

I hope this helps.
Amanda

 

Training courses

 

Training information:

See also:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel tip:

DATEDIF function

The DATEDIF function is a worksheet function that computes the difference between two dates. This function is documented only in the help files for Excel 2000. It isn't documented in Excel 2002.
the formula function is:
=DATEDIF(Your age,Today(),"Y")
Y stands for year

View all Excel hints and tips


Server loaded in 0.1 secs.