Excel Hyperlink: how to create, change and remove

The tutorial explains how to hyperlink in Excel by using 3 different methods. You will learn how to insert, change and remove hyperlinks in your worksheets and now to fix non-working links.

Hyperlinks are widely used on the Internet to navigate between web-sites. In your Excel worksheets, you can easily create such links too. In addition, you can insert a hyperlink to go to another cell, sheet or workbook, to open a new Excel file or create an email message. This tutorial provides the detailed guidance on how to do this in Excel 2016, 2013, 2010 and earlier versions.

An Excel hyperlink is a reference to a specific location, document or web-page that the user can jump to by clicking the link.

Microsoft Excel enables you to create hyperlinks for many different purposes including:

  • Going to a certain location within the current workbook
  • Opening another document or getting to a specific place in that document, e.g. a sheet in an Excel file or bookmark in a Word document.
  • Navigating to a web-page on the Internet or Intranet
  • Creating a new Excel file
  • Sending an email to a specified address

Hyperlinks in Excel are easily recognizable - generally this is text highlighted in underlined blue like shown in the screenshot below.
Hyperlinks in Excel

Absolute and relative hyperlinks in Excel

Microsoft Excel supports two types of links: absolute and relative, depending on whether you specify a full or partial address.

An absolute hyperlink contains a full address, including the protocol and domain name for URLs, and the entire path and file name for documents. For example:

Absolute URL: https://www.ablebits.com/excel-lookup-tables/index.php

Absolute link to an Excel file: C:\Excel files\Source Data\Book1.xlsx

A relative hyperlink contains a partial address. For example:

Relative URL: excel-lookup-tables/index.php

Relative link to an Excel file: Source data\Book3.xlsx

On the web, it's a common practice to use relative URLs. In your Excel hyperlinks, you should always supply full URLs for web-pages. Though, Microsoft Excel can understand URLs without a protocol. For example, if you type "www.ablebits.com" in a cell, Excel will automatically add the default "http" protocol and convert it into a hyperlink you can follow.

When creating links to Excel files or other documents stored on your computer, you can use either absolute or relative addresses. In a relative hyperlink, a missing part of the file path is relative to the location of the active workbook. The main advantage of this approach is that you don't have to edit the link address when the files are moved to another location. For example, if your active workbook and target workbook reside on drive C, and then you move them to drive D, relative hyperlinks will continue working as long as the relative path to the target file remains unchanged. In case of an absolute hyperlink, the path should be updated every time the file is moved to another place.

In Microsoft Excel, the same task can often be accomplished in a few different ways, and it is also true for creating hyperlinks. To insert a hyperlink in Excel, you can use any of the following:

The most common way to put a hyperlink directly into a cell is by using the Insert Hyperlink dialog, which can be accessed in 3 different ways. Just select the cell where you want to insert a link and do one of the following:

  • On the Insert tab, in the Links group, click the Hyperlink or Link button, depending on your Excel version.
    Insert a hyperlink in Excel by clicking the ribbon button.
  • Right click the cell, and select Hyperlink… (Link in recent versions) from the context menu.
    Create a hyperlink by using the right-click menu.
  • Press the Ctrl + K shortcut.

And now, depending on what sort of link you want to create, proceed with one of the following examples:

To insert a hyperlink to another document such as a different Excel file, Word document or PowerPoint presentation, open the Insert Hyperlink dialog, and perform the steps below:

  1. On the left-hand panel, under Link to, click the Existing File or Web Page
  2. In the Look in list, browse to the location of the target file, and then select the file.
  3. In the Text to display box, type the text you want to appear in the cell ("Book3" in this example).
  4. Optionally, click the ScreenTip… button in the upper-right corner, and enter the text to be displayed when the user hovers the mouse over the hyperlink. In this example, it's "Goto Book3 in My Documents".
  5. Click OK.

Create a hyperlink to another document

The hyperlink is inserted in the selected cell and looks exactly as you've configured it:
The hyperlink is inserted in the selected cell.

To link to a specific sheet or cell, click the Bookmark… button in the right-hand part of the Insert Hyperlink dialog box, select the sheet and type the target cell address in the Type in the cell reference box, and click OK.
Creating a hyperlink to a specific cell

To link to a named range, select it under Defined names like shown below:
Creating a link to a named range

To create a link to a web page, open the Insert Hyperlink dialog, and proceed with the following steps:

  1. Under Link to, select Existing File or Web Page.
  2. Click the Browse the Web button, open the web page you want to link to, and switch back to Excel without closing your web browser.

Excel will insert the web site Address and Text to display for you automatically. You can change the text to display the way you want, enter a screen tip if needed, and click OK to add the hyperlink.
Adding a hyperlink to a web address

Alternatively, you can copy the web page URL before opening the Insert Hyperlink dialog, and then simply paste the URL in the Address box.

To create a hyperlink to a specific sheet in the active workbook, click the Place in this Document icon. Under Cell Reference, select the target worksheet, and click OK.
Link to a sheet in the current workbook.

To create an Excel hyperlink to cell, type the cell reference in the Type in the cell reference box.

To link to a named range, select it under the Defined Names node.

Besides linking to existing files, you can create a hyperlink to a new Excel file. Here's how:

  1. Under Link to, click the Create New Document icon.
  2. In the Text to display box, type the link text to be displayed in the cell.
  3. In the Name of new document box, enter the new workbook name.
  4. Under Full path, check the location where the newly created file will be saved. If you want to change the default location, click the Change button.
  5. Under When to edit, select the desired editing option.
  6. Click OK.

Insert a hyperlink to open a new workbook

Apart from linking to various documents, the Excel Hyperlink feature allows you to send an email message directly from your worksheet. To have it done, follow these steps:

  1. Under Link to, select the E-mail Address icon.
  2. In the E-mail address box, type the e-mail address of your recipient, or multiple addresses separated with semicolons.
  3. Optionally, enter the message subject in the Subject box. Please keep in mind that some browsers and e-mail clients may not recognize the subject line.
  4. In the Text to display box, type the desired link text.
  5. Optionally, click the ScreenTip… button and enter the text you want (the screen tip will be displayed when you hover over the hyperlink with the mouse).
  6. Click OK.

Add a hyperlink to create an email message

Tip. The fastest way to make a hyperlink to a specific e-mail address it to type the address directly in a cell. As soon as you hit the Enter key, Excel will automatically convert it into a clickable hyperlink.

If you are one of those Excel pros that employ formulas to tackle most of the tasks, you can use the HYPERLINK function, which is specially designed to inset hyperlinks in Excel. It is particularly useful when you intend to create, edit or remove multiple links at a time.

The syntax of the HYPERLINK function is as follows:

HYPERLINK(link_location, [friendly_name])

Where:

  • Link_location is the path to the target document or web-page.
  • Friendly_name is the link text to be displayed in a cell.

For example, to create a hyperlink titled "Source data" that opens Sheet2 in the workbook named "Source data" stored in the "Excel files" folder on drive D, use this formula:

=HYPERLINK("[D:\Excel files\Source data.xlsx]Sheet2!A1", "Source data")

For the detailed explanation of the HYPERLINK function arguments and formula examples to create various types of links, please see How to use Hyperlink function in Excel.

To automate the creation of hyperlink in your worksheets, you can use this simple VBA code:

Public Sub AddHyperlink() Sheets("Sheet1").Hyperlinks.Add Anchor:=Sheets("Sheet1").Range("A1"), Address:="", SubAddress:="Sheet3!B5", TextToDisplay:="My hyperlink" End Sub

Where:

  • Sheets - the name of a sheet on which the link should be inserted (Sheet 1 in this example).
  • Range - a cell where the link should be inserted (A1 in this example).
  • SubAddress - link destination, i.e. where the hyperlink should point to (Sheet3!B5 in this example).
  • TextToDisplay -text to be displayed in a cell ("My hyperlink" in this example).

Given the above, our macro will insert a hyperlink titled "My hyperlink" in cell A1 on Sheet1 in the active workbook. Clicking the link will take you to cell B5 on Sheet3 in the same workbook.

If you have little experience with Excel macros, you may find the following instructions helpful: How to insert and run VBA code in Excel

If you created a hyperlink by using the Insert Hyperlink dialog, then use a similar dialog to change it. For this, right-click a cell holding the link, and select Edit Hyperlink… from the context menu or press the Crtl+K shortcut or click the Hyperlink button on the ribbon.
Editing a hyperlink in Excel

Whichever you do, the Edit Hyperlink dialog box will show up. You make the desired changes to the link text or link location or both, and click OK.

To change a formula-driven hyperlink, select the cell containing the Hyperlink formula and modify the formula's arguments. The following tip explains how to select a cell without navigating to the hyperlink location.

To change multiple Hyperlink formulas, use Excel's Replace All feature as shown in this tip.

How to change a hyperlink appearance

By default, Excel hyperlinks have a traditional underlined blue formatting. To change the default appearance of a hyperlink text, perform the following steps:

  1. Go to the Home tab, Styles group, and either:
    • Right-click Hyperlink, and then click Modify… to change the appearance of hyperlinks that have not been clicked yet.
    • Right-click Followed Hyperlink, and then click Modify… to change the formatting of hyperlinks that have been clicked.

    Select whether to change the appearance of followed or non-followed hyperlinks.

  2. In the Style dialog box that appears, click Format…
    Click the Format button.
  3. In the Format Cells dialog, switch to the Font and/or Fill tab, apply the options of your choosing, and click OK. For example, you can change the font style and font color like shown in the screenshot below:
    Select the formatting options of your choosing.
  4. The changes will be immediately reflected in the Style dialog. If upon a second thought, you decide not to apply certain modifications, clear the check boxes for those options.
    Check the boxes only for those options that you want to apply.
  5. Click OK to save the changes.

Note. All changes made to the hyperlink style will apply to all hyperlinks in the current workbook. It is not possible to modify formatting of individual hyperlinks.

Removing hyperlinks in Excel is a two-click process. You simply right-click a link, and select Remove Hyperlink from the context menu.
Removing a hyperlink in Excel

This will remove a clickable hyperlink, but keep the link text in a cell. To delete the link text too, right-click the cell, and then click Clear Contents.

Tip. To remove all or selected hyperlinks at a time, use the Paste Special feature as demonstrated in How remove multiple hyperlinks in Excel.

Now that you know how to create, change and remove hyperlinks in Excel, you may want to learn a couple of useful tips to work with links most efficiently.

By default, clicking a cell that contains a hyperlink takes you to the link destination, i.e. a target document or web-page. To select a cell without jumping to the link location, click the cell and hold the mouse button until the pointer turns into a cross (Excel selection cursor) Excel selection cursor, and then release the button.

If a hyperlink occupies just part of a cell (i.e. if your cell is wider than the link's text), move the mouse pointer over the whitespace, and as soon as it changes from a pointing hand to a cross, click the cell:
Select a cell containing a hyperlink without jumping to the link destination

One more way to select a cell without opening a hyperlink is to select a neighboring cell, and use the arrow keys to get to the link cell.

There are two ways to extract a URL from a hyperlink in Excel: manually and programmatically.

Extract a URL from a hyperlink manually

If you have just a couple of hyperlinks, you can quickly extract their destinations by following these simple steps:

  1. Select a cell containing the hyperlink.
  2. Open the Edit Hyperlink dialog by pressing Ctrl + K, or right-click a hyperlink and then click Edit hyperlink….
  3. In the Address field, select the URL and press Ctrl + C to copy it.
    Copy the hyperlink address
  4. Press Esc or click OK to close the Edit Hyperlink dialog box.
  5. Paste the copied URL into any empty cell. Done!

Extract multiple URLs by using VBA

If you have a great lot of hyperlinks in your Excel worksheets, extracting each URL manually would be a waste of time. The following macro can speed up the process by extracting addresses from all hyperlinks on the current sheet automatically:

Sub ExtractHL() Dim HL As Hyperlink Dim OverwriteAll As Boolean OverwriteAll = False For Each HL In ActiveSheet.Hyperlinks If Not OverwriteAll Then If HL.Range.Offset(0, 1).Value <> "" Then If MsgBox("One or more of the target cells is not empty. Do you want to overwrite all cells?", vbOKCancel, "Target cells are not empty") = vbCancel Then Exit For Else OverwriteAll = True End If End If End If HL.Range.Offset(0, 1).Value = HL.Address Next End Sub

As shown in the screenshot below, the VBA code gets URLs from a column of hyperlinks, and puts the results in the neighboring cells.
Multiples URLs are extracted at a time

If one or more cells in the adjacent column contains data, the code will display a warning dialog asking the user if they want to overwrite the current data.

Apart from text in a cell, many worksheet objects including charts, pictures, text boxes and shapes can be turned into clickable hyperlinks. To have it done, you simply right-click an object (a WordArt object in the screenshot below), click Hyperlink…, and configure the link as described in How to create hyperlink in Excel.
Use the object's context menu to make it a hyperlink.

Tip. The right-click menu of charts does not have the Hyperlink option. To convert an Excel chart into a hyperlink, select the chart, and press Ctrl + K.

If hyperlinks are not working properly in your worksheets, the following troubleshooting steps will help you pin down the source of the problem and fix it.

Reference isn't valid

Symptoms: Clicking a hyperlink in Excel does not take the user to the link destination, but throws the "Reference isn't valid" error.
Reference isn't valid.

Solution: When you create a hyperlink to another sheet, the sheet's name becomes the link target. If you rename the worksheet later, Excel won't be able to locate the target, and the hyperlink will stop working. To fix this, you need to either change the sheet's name back to the original name, or edit the hyperlink so that it points to the renamed sheet.

If you created a hyperlink to another file, and later moved that file to another location, then you will need to specify the new path to the file.

Hyperlink appears as a regular text string

Symptoms: Web-addressed (URLs) typed, copied or imported to your worksheet are not converted into clickable hyperlinks automatically, nor are they highlighted with a traditional underlined blue formatting. Or, links look fine but nothing happens when you click on them.

Solution: Double-click the cell or press F2 to enter the edit mode, go to the end of the URL and press the Space key. Excel will convert a text string into a clickable hyperlink. If there are many such links, check the format of your cells. Sometimes there are issues with links placed in cells formatted with the General format. In this case, try changing the cell format to Text.

Hyperlinks stopped working after reopening a workbook

Symptoms: Your Excel hyperlinks worked just fine until you saved and reopened the workbook. Now, they are all grey and no longer work.

Solution: First off, check if the link destination has not been changed, i.e. the target document was neither renamed nor moved. If it's not the case, you may consider turning off an option that forces Excel to check hyperlinks every time the workbook is saved. There have been reports that Excel sometimes disables valid hyperlinks (for example, links to files stored in your local network may be disabled because of some temporary problems with your server.) To turn off the option, follow these steps:

  1. In Excel 2010, Excel 2013 and Excel 2016, click File > Options. In Excel 2007, click the Office button > Excel Options.
  2. On the left panel, select Advanced.
  3. Scroll down to the General section, and click the Web Options…
  4. In the Web Options dialog, switch to the Files tab, clear the Update links on save box, and click OK.

Clear the Update links on save box.

Formula-based hyperlinks do not work

Symptoms: A link created by using the HYPERLINK function does not open or displays an error value in a cell.

Solution: Most problems with formula-driven hyperlinks are caused by a non-existent or incorrect path supplied in the link_location argument. The following examples demonstrate how to create a Hyperlink formula properly. For more troubleshooting steps, please see Excel HYPERLINK function not working.

This is how you create, edit and remove a hyperlink in Excel. I thank you for reading and hope to see you on our blog next week!

Table of contents

45 comments

  1. I can't find the option to create a new file in my 'Insert Hyperlink' dialog box. Please help me with this i am using excel 2021 version.

  2. Hi,
    Is there a vb way to bulk update hyperlinks where the hyperlink uses "Place in this Document". ??
    The vb I've found to date (using .address) only updates the text to display but not the hyperlink to the other worksheets.

  3. Help Urgently required :)
    i have excel sheet with a lot (i mean it) cells in column with values that have hyperlink created from dropdown menu. linked files had to be moved and now I end up with links that do not work.

    example: C:\Users\XXXXX\AppData\Roaming\Microsoft\04 - PROJECTS\XXX this is what i get as link now and correct path should be
    S:\Personal Folders\04 - PROJECTS\XXX

    is there a way to change automatically as i have more then 500 entries

  4. I used to be able to have a Column A be a hyperlink and Column B be text (the specific record ID) then use the "&" sign to concatenate both into Column C. I would then copy the format of Column A to Column C and my hyperlink would still work. That stopped working recently. Does anyone know why?

  5. I am creating a directory of sorts in excel, where Sheet1 has basic information and hyperlinks to more information or resources, I have added Sheet2 to Copy and paste numerous txt documents of info, in blocks. I am using Hyperlink in sheet1 in excel to reference specific cells in sheet2 where the correlating block of info is located. The issue I am running into is that when I am father down in the excel sheet i.e. A64 (Sometimes seems random the farther down it goes) the hyperlink takes me there but shows A64 at the bottom of the screen. The first few cells A1, A20, A40 all take me to the cell located as the first or second cell seen, which is what I want, so the block of information (The next 10-15 rows) is seen without having to scroll or mistake it with the block of information above it.

    Easy Answer is to just space out the blocks more, but I was hoping there might be a fix or adjustment within Excel.

  6. I have over 40 hyperlinks (1 per person), which are referenced in my workbook because I need to pull arrays from individuals' sheets that are on Sharepoint. I need to update the same part of every hyperlink when the file location on Sharepoint changes.

    Examples of a hyperlink are:
    h ttp://companysite.sharepoint.com/Site1/OEBKSD/5435375/Person1/[filename.xlsm]Tab Name'!$A$2:$D$75

    h ttp://companysite.sharepoint.com/Site1/OEBKSD/5435375/Person2/[filename.xlsm]Tab Name'!$A$2:$D$75

    Examples of a location change are:
    h ttp://companysite.sharepoint.com/Site2/PSEMS/356356/Person1/[filename.xlsm]Tab Name'!$A$2:$D$75
    h ttp://companysite.sharepoint.com/Site2/PSEMS/356356/4B44/Person1/[filename.xlsm]Tab Name'!$A$2:$D$75

    h ttp://companysite.sharepoint.com/Site2/PSEMS/356356/Person2/[filename.xlsm]Tab Name'!$A$2:$D$75
    h ttp://companysite.sharepoint.com/Site2/PSEMS/356356/4B44/Person2/[filename.xlsm]Tab Name'!$A$2:$D$75

    I tried using some suggested VBA code but this did not work for me, which I think is because I am referencing the links to pull data rather than have as a clickable link.

    I would prefer to use VBA to update the link references rather than find and replace, as I would also like to use VBA to open then close the target files on Sharepoint so that my workbook gets updated accordingly (the arrays don't get updated unless the target files are open).

    I'm on a Mac, so using Power Query to retrieve data from Sharepoint is not an option for me. What VBA code should I use for this?

  7. I have an Excel sheet with a URL in the first column and the friendly name in the second column. I created a HYPERLINK formula in the third column; all good. Now I want to get rid of the first two columns, and have the hyperlink stand on its own.

    I copied all the cells with the hyperlink formulas, then used Paste Special / Values. All I got was the text of the cell, with no hyperlink.

    I would rather not have to create each hyperlink individually. How do I create a series of hyperlink formulas, get rid of the source cells, and keep the hyperlinks?

  8. I'm working on particular workbook. The workbook contain several worksheet with accounting formulas, i have linked each sheet with hyperlinks to one sheet in the workbook what formula can i use to automatically input the new copied sheet to the sheet that contains the hyperlinks.

  9. I have both WPS and MS365 for doing .xlsx files. For a long time, I have been using only WPS (I prefer their UI), and all the default pgm settings for xls, xlsx files points to WPS. My HYPERLINKs (both feature and function) open xlsx files in WPS, which is what I want.
    Lately, I have been trying MS365 to learn the new functions (not yet in WPS), as well as PowerQuery etc. Once I started saving xlsx files in MS365, my hyperlinks now opens xlsx files in MS365 (which I don't want). I have checked the default pgm settings, etc, and all still points to WPS. Opening from WinExplorer, selecting from file right-click context menus all uses WPS. Only my hyperlinks insists on using MS365. Unchecking "update links on saving" option (only available in MS365) as mentioned did not help. I even opened the file in WPS, and save, hoping it would reset some flag.
    I suspect there must be a separate registry entry that control this, or could Hyperlinks use some weird file extension's default for opening xlsx files.
    Any help would be greatly appreciated. Many thanks in advance.

  10. As always, thank you, Svetlana, for your thorough articles!

    You mention "If a hyperlink occupies only some part of a cell..." - could you elaborate on how to toggle this functionality?

    • Hyperlink occupying entire cell.
    VS.
    • Hyperlink applying only to the text and not the white space of the cell.

    _Thanks!_

    • Hi Sergey,

      When a link is created by using the Link feature or HYPERLINK function, only the text is a clickable hyperlink. In that sentence, I actually meant "If a hyperlink's text occupies only some part of a cell..." i.e. if the cell is wider than the text of the hyperlink. Sorry for the confusion, I will try to reword it.

      To make an entire cell a hyperlink, you can create a shape, size it exactly to the size of your cell, and link the shape (right-click the shape > Link).

  11. can anyone answer if my below concern is possible? and if yes how?

    i have multiple link in column A, then each of the cell in column A is I have preferred name that is written on column B.
    the question is, can i create a formula to make the preferred name open the same url as equivalent to links in column A? I know that this can be done individually by just editing name of the link/url in column A, but I'm wondering if this is also possible via formula so i dont need to do it one by one.

  12. thanks ..

  13. Hi - I want to add a hyperlink in excel but the URL is to long. Can I use CONCATENATE with the HYPERLINK function and if so how?

    Thanks

    • Hello!
      I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what formula you used and what problem or error occurred. Give an example.
      It’ll help me understand it better and find a solution for you.

  14. hi
    i create hyperlink but when i insert cell hyperlink loction change i dont want change it

  15. M workbook has two sheets. I'm trying to connect cells on Sheet 2 to corresponding cells on Sheet 1 with a hyperlink. That works fine until I insert a new row on sheet 1. Here's an example of the problem: When I click Sheet 2 A10 I want to go back to Sheet 1 A10. These cells have related data. If I inset a row before Sheet 1 A10, when I click on Sheet 2 A10 I'm taken to the new data now in Sheet 1 A10, when I really should be at Sheet 1 A11. I tried using relative references in the link ($A$10), but Excel ignores that. There must be a simple way to fix this. Any gui8dance most appreciated!

    • Hello!
      When you insert rows or columns into your worksheet, your hyperlinks won't change. This is how they differ from regular cell references.

      • Ohhh... I was afraid of that might be the case. So there's no clever workaround for this?

  16. Awesome hi and amazing article like loaded with full of exceptionally valuable
    details. You're just amazing. Keep sharing a lot more relating to this.
    You wish to learn more from you and regularly!

  17. Hi I would like to extract the hyperlink which text is an addresses from a webpage of property auction results and insert all of the hyperlinks into an excel page as i currently have to select all and then copy and paste into excewl sheet and then edit manually and manually search individual postcodes to match suburb and it is extremely time consuming. look forward to your assistance if possible. thank you in advance.

  18. I am having more than 5000 hyperlinks in my document. Thing were working fine till last week however when I opened my sheet this week all of the hyperlinks are having error CANNOT OEENED THE SPECIFIED FILE. No changes in file path has been done. Now all my links are gone. Request to help

  19. How to insert hyperlink in Excel by using VBA: how the code will change if I want to hyperlink not only cell A1 but the whole column A :A2--> B6, A3 to B7 and so on especially if the column is more than 1000 rows. Thank you

  20. All hyperlinks in my Excel file have Changed to the following path
    C:\Users\hamidreza\AppData\Roaming\Microsoft\Excel
    The main path is on the network drive map \\filesrv\...
    This change of course has happened suddenly.
    1-Why is this happening?
    2-How to correct the paths to the main path in the network drive?
    The number of Hyperlinks is high
    Please help.
    Thanks

  21. Will changing the File option "update links on saving" - to unchecked remain in place specific to the file? Is this truly a file specific property or a property of the excel session or instance that has the file open.

  22. Can anyone tell me whether if changing the File option "update links on saving" - to unchecked is a setting specific and remain with this File as unchecked? My question is whether this is truly a file specific property or a property of the excel session or instance that has the file open.

    • "My question is whether this is truly a file specific property or a property of the excel session or instance that has the file open."
      Probably too late for whatever you are doing, but this is a property that is an Excel setting, not related to any specific file. This is not set per-file, it's set Excel-wide.

  23. Please advise how to change to default searchable File Name format from “Office Files” to “All Files” in "Link to File" browser (Inserting Hyperlink & selecting "Browse for file").

  24. Mam,
    hyperlinked some photos to excel sheet in my pc. but when i send the same to my boss the the hyperlink is not found. how to solve this issue please

  25. Hoping for some help please. Trying to create a simple index sheet with hyperlinks to each worksheet. The sheet is updated but clicking the link throws "Reference isn't valid." Stepping through debug shows LinkName with the correct worksheetname. However, viewing in Edit Hyperlink shows all links pointing to cell reference "Index." Any & all help very much appreciated.

    For Each S In Worksheets

    ' Ignored sheets
    If S.Visible = -1 And S.Name "Selections" And S.Name "Guide" _
    And S.Name "Cover" And S.Name "TOC" And S.Name "Index" _
    And Not S.Name Like "Sheet*" _
    Then
    S.Select

    ' Create link name using footer data + cell A1 data
    ThisName = ActiveSheet.PageSetup.RightFooter + " -- " + Range("a1").Value

    ' Get rid of control chars
    ThisName = Mid(ThisName, 3)

    ' Create link
    LinkName = S.Name & "!A1"

    With Sheets("Index")
    .Select
    .Hyperlinks.Add Anchor:=ActiveSheet.Cells(TOCRow, 1), Address:="", _
    SubAddress:=LinkName, TextToDisplay:=ThisName
    End With

    TOCRow = TOCRow + 1 'Bump row nbr
    End If
    Next S

  26. Below works great, thank you!
    My hyperlinks kept changing when I executed a macro; e.g. backup the workbook.

    =HYPERLINK("[D:\Excel files\Source data.xlsx]Sheet2!A1", "Source data")

  27. I love it...Thanks

  28. can anyone help me with some date formula, i wanted to put formula in cell A1 where when you write anything on cell B1, the cell A1 will automatically generates a date for that day.... and on the next day, when you write on cell B2 the the cell A2 will automatically generate a date on the same day but will not affect the cell A1... meaning if today is January 1, 1900 and write anything on cell B1, cell A1 will be dated January 1, 1900 and when i write tomorrow on cell B2, cell A2 will be dated January 2, 1900 but cell A1 will still be January 1, 1900.... hope you can help me.

    • Hello, Ronald
      unfortunately, there's no such formula. TODAY is the function that could generate the date of entering the data
      =IF(ISBLANK(B1),"",TODAY())
      but it would change every day when you work with a document.

    • Probably too later for Ronald, but if anyone else needs how to do this, this code should do the trick. If you need different cells just modify the range for A (cell you are entering data into) and the offset (cell where you want the date filled) as needed.

      Private Sub Worksheet_Change(ByVal Target As Range)
      Dim A As Range, B As Range, Inte As Range, r As Range
      Set A = Range("B1:B355")
      Set Inte = Intersect(A, Target)
      If Inte Is Nothing Then Exit Sub
      Application.EnableEvents = False
      For Each r In Inte
      If r.Offset(0, -1).Value = "" Then
      r.Offset(0, -1).Value = Date
      End If
      Next r
      Application.EnableEvents = True
      End Sub

  29. Woohooo, let's get into more VBA code for automating processes.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)