Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
add comment

4 Answers

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

share|improve this answer
    
Thank you thank you thank you.... and I could even sort-of understand it... –  Gill Jul 24 '12 at 4:57
    
I came to say the same thing. Slight variation: =HYPERLINK("#"&CELL("address",Sheet2!A5),"Click for continued series") Treat the Sheet2!A5 part like a regular cell reference in a formula, and change the "Click for continued series" part to the text that you would like. Edit: I've tried editing this multiple times to include the formula on its own line but haven't figured out how - sorry for the block of text. –  Levi Nov 4 '13 at 23:26
add comment

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
share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
    
Hmm, I'm afraid I couldn't get my head round this one. I'm probably doing something wrong. Thanks for trying anyway. –  Gill Jul 24 '12 at 4:57
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.