How can I copy a hyperlink across multiple cells (down a column) with the target cell in the hyperlink adjusted relatively (similar to copying formulae)? I have a very long list of rows making copying and editing impractical. The hyperlink is to move between sheets e.g. - =HYPERLINK("#‘Sheet2’.A335",“Next Page”) to move from Sheet1 to sheet2. If I try to copy using the cross hair pointer and dragging the cells down, they are filled with exact copies without updating the target cell row reference.
Hello,
you can’t without a helper column (here: column A) containing the reference, since the first argument to function HYPERLINK() is text and not a reference (and only references are adapted when using drag down)
A **solution** to your task could be something like:
=HYPERLINK(TEXT("#Sheet2.A" & ROW() + 334;"@");"Next Page - Cell: A" & ROW() + 334), which is the ever same formula and which defines an offset of 334, if you start using it on A1 in Sheet1 (of course you need to adapt the offset to your real situation. It just to show the idea to use one formula for all rows. If you don’t use an offset, then the formula links to the same row in Sheet2 where the formula is used in Sheet1).
Please see the following sample file: HyperLink-By-Formula.ods
Hope that helps.
Thank you for this answer which perfectly achieves the result I wanted. I also appreciated that you set it out to in a manner which enabled me to understand how the answer achieved the result.
If it is helpful please consider to click the check mark (
) next to the answer. Thanks in advance …