The easiest way to do this is to define a name for the range of cells
in Sheet3 (c1100:m1121) and use that defined name for the hyperlink.
As an example, I created a new Excel file (with three worksheets) and
did the following steps:
- entered some sample data in $B$2 through $B$4 on Sheet2
- selected those three cells
- used the menu Insert -> Name -> Define... and entered Stuff as the
name of the range
- on Sheet1, I created two hyperlinks
o the first to the cells $B$2:$B$4 on Sheet2
o the second to the defined name Stuff
- I tested both hyperlinks and they both worked OK
- on Sheet2 I inserted a row that added a blank cell between the
values on that sheet
- I tested both hyperlinks and saw the following:
o the first still points to the cells as orginally defined (as you have seen)
o the second points to the expanded range, including the blank cell
As long as you add rows (or columns) within the middle of the range,
the defined name will work as expected. If you add a row or column at
the beginning or after the end of the range, the range is not
If you need to add rows at the end (and have some space...), be sure
to define the range to a row / column beyond the last data value. I
usually apply a color (e.g., the light yellow) as a reminder. You can
then insert rows / columns anywhere up to the yellow cells and the
range will adjust automatically.
Please make a clarification request if any part of the answer is
unclear or incomplete.
For more information on defined names in Excel, use the online help
using a phrase like
and you will have several good references with further explanations.