Copying a hyperlink so that it works with a series

Question

OK, this should be a no-brainer, but I cannot find anyone to help me with this: Example A5-K5 On worksheet 1 contains info relating to one specific item. I want to have a hyperlink in L5 taking me to A5 on worksheet 2 where the relevant data relating to that item continues on the next worksheet. Next row of data will need it's own hyperlink to the next cell down on worksheet 2 (A6).

Doing a hyperlink up manually for every row is a total pain. How do I copy the hyperlink and paste it so that it picks up the "series" information?

Answer 1

There is an Excel function called HYPERLINK. It allows you to create a hyperlink using a formula. By using this, you can create a hyperlink based on a "series". If you use the HYPERLINK function in combination with the ADDRESS function, you can create a hyperlink that can be "copied down" to automatically shift its cell reference.

In Excel, hyperlinks that link to a cell reference are internally stored with a # sign as a prefix. For example, a hyperlink that will take you to cell A5 on Sheet2 would look like this:

#Sheet2!A5

You can take advantage of this to create a formula that is automatically shifted when it is copied, as follows:

=HYPERLINK("#"&ADDRESS(5,1,4,1,"Sheet2"))

This creates a hyperlink to a pound sign, followed by the reference returned by the ADDRESS function. Using the ADDRESS function gives you the advantage of being able to have the formula 'shift' itself when copied into a different location. However, in order to take advantage of this, you must use the ROW or COLUMN functions to specify a relative cell address in the formula. This is because the 'fixed' numbers given in the regular ADDRESS formula will not 'shift' when copied, because they are not cell references. Here is an example:

=HYPERLINK("#"&ADDRESS(ROW(J5),1,4,1,"Sheet2"))

This formula gives the same result as the one above, but it will automatically shift if it is copied downward, because of the relative reference to J5 that it contains (ROW(J5) returns 5 because the cell J5 is located in row 5). If you wanted to have it shift to the right and left as well as up and down, you could replace the second argument of the ADDRESS function (1) with COLUMN(A10).

Here is an example spreadsheet. Try copying the formula and pasting it down column A.

https://dl.dropbox.com/u/3488338/Hyperlink.xlsx

Answer 2

I had the same problem and tried the solution above:

Start by picking a cell somewhere out of sight on Sheet1. For example, assuming we are going to start the links with cell L5, we can choose cell AA5, which should be sufficiently off the page, as our target. Start by entering this text into L5: =HYPERLINK(AA5, "Details") This will put the word "Details" in the cell and it will be a hyperlink that checks cell AA5 to find the place it should link to.

I could only get that method to move the selected cell to the cell which had the hyper link I wanted in it! What I needed to do is to copy a hyperlink made in one cell to another so the same hyperlink to the same file/location/etc works in another cell, without having to manually recreate the hyperlink.

A very simple low tech fix for people like me who prefer a solution that uses things they already know like copy and paste:

1. Manually create a hyperlink by right clicking or hitting Ctrl+K.
   EG: connect cells A1 to A3 to documents A1Test to A3Test.
2. Copy the hyperlinked cells.
   EG: Copy A1 to A3
3. Paste hyperlinked cells in an unused column
   EG: Paste to D1 to D3
4. Copy the the un-hyperlinked cells to which you
   EG: Copy range B1 to B3, which contains document names "Test 1" to "Test 3"
5. Paste special>Values onto the unused columns
   EG: Select range D1 to D3 and Paste special over the hyperlinked cells in the unused column
6. Copy the newly hyperlinked cells from the unused column to the original column, so now you have the same hyperlinks but with different text
   EG: Copy D1 to D3 and paste over B1 to B3

Answer 3

Let me take a stab at explaining how to do this. I also struggled for hours to do something that should have been as simple as setting up a simple formula and copying it down like any other.

What you first need to do is set up a column of text for the hyperlink formula to reference. In this case it will start in A1

Type #Sheet2!A1 in cell A1.

When you copy this cell down Excel is smart enough to increase the row number by one.

      A
1 #Sheet2!A1 
2 #Sheet2!A2 
3 #Sheet2!A3

...

Then when you set up =HYPERLINK(A1, "link text") you can copy this down and the A1 reference will increase like any other formula.

Answer 4

As you have notice, Excel likes to play dumb when it comes to hyperlinks. However, there is a way to fool it into doing what you want.

Start by picking a cell somewhere out of sight on Sheet1. For example, assuming we are going to start the links with cell L5, we can choose cell AA5, which should be sufficiently off the page, as our target. Start by entering this text into L5: =HYPERLINK(AA5, "Details") This will put the word "Details" in the cell and it will be a hyperlink that checks cell AA5 to find the place it should link to. Now drag that cell down and it should automatically fill the series. The row numbers for the cells in column AA should have increased in the series, as you would expect with a normal formula.

After you have filled all the rows you need in column L, enter the following text into AA5 #Sheet2!A5 (substitute the appropriate sheet name, and notice that there is no =). Drag that cell down to fill the appropriate cell series. Return to column L and Success! you should have working hyperlinks!

For whatever reason (known only to Microsoft), if you do these steps in reverse order it doesn't work. When the HYPERLINK formula is "broken" (it references an empty cell) it will fill series without issue. If it is working (it references a cell with a hyperlink-able location) then it acts as you have seen and just copies the same hyperlink over and over.

Answer 5

I use this all the time. In a spare column, put the formula below After that you may drag down the formula to below rows. After that, copy the whole formula text column you just did onto a blank notepad page. Copy again from notepad and back to the worksheet. This removes all excel formatting and return only text. Your hyperlinks will all nicely be formatted for every row. Just change the hyperlink text style. (A2 and B2 are links and link text respectively ):-

="=hyperlink(""" & A2 & """ , """ & B2 & """)"