Excel 2007 : Auto increment hyperlink formula

I have been testing different formulas through trial and error all day, and I just cannot get this to work. So ANY help would be very much appreciated. Please note that I am using Excel 2007.

The Scenario:

I am attempting to create a hyperlink in cell B2 on the 1st sheet (Book 1) that will link to cell range A1:A6 on the 2nd sheet (Book 2).

I thought the formula would be as follows, but apparently I was wrong:

Please Login or Register  to view this content.

And not only does that not link to the cell range as it should, but when trying to drag that formula from B2 through B198, it keeps the same formula, and does not increase in increments.

So the idea is that cell B2 on Book 1 links to cell range A1:A6 on Book 2; cell B3 on Book1 links to cell range B1:B6 on Book 2, etc, etc ...

Any ideas on how I can go about resolving this? I appreciate your help in the advance.

Best Regards,

Matt Dimock

Hello,

from the Excel 2010 help files:

Jump to a different cell in the same workbook
You can create hyperlinks in a worksheet to jump from one cell to another cell in the same workbook. For example, in the workbook Budget.xlsx, the following formula creates a hyperlink to cell E56 in the active worksheet. The link text itself is the value in cell E56.

=HYPERLINK("[Budget.xlsx]E56", E56)To jump to a different sheet in the same workbook, include the name of the sheet, followed by an exclamation point (!), in the link. In the previous example, to create a link to cell E56 on the September sheet, include September! in the link.

=HYPERLINK("[Budget.xlsx]September!E56", E56)

So, the correct syntax for the formula in your case would be

=HYPERLINK("[Auto increment Hyperlink formula.xlsx]'Book 2'!A1:A6",A2)

As to dragging the formula down: When you drag a formula down, only the row will increment, but the column references will stay the same. The references inside the hyperlink string will not increment at all, since they are text. If you want to increment the column references, you will need to build the text string for the hyperlink by concatenating the Address function into the string.

=HYPERLINK("[Auto increment Hyperlink formula.xlsx]'Book 2'!"&ADDRESS(ROW($A$1),ROW(A1),4)&":"&ADDRESS(ROW($A$6),ROW(A1),4),A2)

cheers,

Start your own thread please. Don't post in another member's question. Especially one that is over three years old. You may want to look at the forum rules to understand how this site works.