Ask Your Question

Copy a Hyperlink down column with adjusted cell target

asked 2020-09-20 09:57:27 +0100

IanB8 gravatar image

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.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2020-09-20 17:08:00 +0100

Opaque gravatar image


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)

image description

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: C:\fakepath\HyperLink-By-Formula.ods

Hope that helps.

edit flag offensive delete link more


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.

IanB8 gravatar imageIanB8 ( 2020-09-21 11:24:29 +0100 )edit

If it is helpful please consider to click the check mark (✔) next to the answer. Thanks in advance ...

Opaque gravatar imageOpaque ( 2020-09-21 17:49:51 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-09-20 09:57:27 +0100

Seen: 22 times

Last updated: Sep 20