Copy/Paste Hyperlinks in a Series & Link Two Separate Cells to Each Other

[snaplink22]

Hello,

I have two actions I would like to apply to my workbook using hyperlinks.

1.) In Sheet 1 I want to create a link in cell B2 that links to cell B2 in Sheet 2 and visa versa, meaning that both links would refer back to the other; both of these cells will contain data.

2.) I am attempting to copy a hyperlink in a series, instead of manually creating a hyperlink for each new entry in the series. So if I copy Row 2, using the above example, and paste it in Row 3 the hyperlink should link to Row 3 in the second sheet.

If possible, I would not want to create a hyperlink formula inside of any of the cells on the sheet. I believe I can achieve this by first creating a hyperlink and then editing it, but all the formulas I've tried haven't quite worked out.

 

[Dave87]

1. if you want 2 cells to equal each other you'll have a circular reference and excel wont be able to calculate, it will return an error
2. use formula =HYPERLINK(Sheet2!a3,"Insert Hyperlink Name Text you want to see here") and copy this down

 

[snaplink22]

1. if you want 2 cells to equal each other you'll have a circular reference and excel wont be able to calculate, it will return an error
2. use formula =HYPERLINK(Sheet2!a3,"Insert Hyperlink Name Text you want to see here") and copy this down

Thanks Dave!

 

[Black Vinyl]

Hi Dave, (or anyone else who can assist),
I have a very similar issue as indicated by snaplink22 and I already have that formula that you suggested Dave87, but when I try to copy down, it doesn't work as expected.
For instance, I have 3 cells (A9, A10, A11) with that formula referencing the corresponding cells in another sheet.
These were manually entered and work as expected when I click the hyperlink.

However, when I select all 3 cells plus another additional say 30 cells and press CTRL D, it copies down the formula of cell A9 all the way down. When I select only the 3 cells with the formula and double click the bottom right hand corner, it copies down the same formula. That is, what's in A9 will appear in A12, A15, A18 etc and what's in A10 is copied to A13, A16, A19 etc.
I want each cell to be updated consecutively. I don't want to be manually entering this hyperlink formula on both sheets for hundreds of cells.
Is there any way I can do this?

Thanks,
BV

 

[Dave87]

what hyperlink text do you want to see in the cells highlighted - do you wish to see "JOB ID 01" right through to "JOB ID 99"? is JOB ID linked to a cell or some other criteria?

I suggest you post this as a new thread but with the above information provided. It may be something as simple as =HYPERLINK(Sheet2!a3,"JOB ID "&Row()-8) but without you providing more information i (or anyone else trying to help) will not know the answer.

 

[Black Vinyl]

Hi Dave87,
Thanks for your reply.
I didn't create a new thread because this one was almost identical to my issue and I didn't think a new thread was warranted. Happy to start a new one if that's the way to go.
Just in answer to your question, happy for the text shown to be Job ID 01, Job ID 02, ... etc. These will be created instantly up to about 5000 I guess. We'll see how we go with job number entries.
The link for Job ID 01 etc is a hyperlink to another sheet and row which also lists Job ID 01 and is hyperlinked back to the original sheet and cell.
So in Sheet 1 the formula reads... =HYPERLINK("#Sheet2!I9","Job ID 1") and on the same row on the other sheet, cell A9 has this formula... =Sheet1!A9 to display the same job number ID but is also hyperlinked back to cell A9 with the same ID on sheet 1.
I want it so that you can click on the Job ID on one sheet and it takes you to the corresponding Job on the second sheet, and vice versa from the second sheet back to the first.
It seems I managed to do this, but I don't want to manually enter 5000 hyperlinks to go from sheet 1 to sheet 2 and then 5000 hyperlinks back from sheet 2 to sheet 1.
I thought that I could Copy down these hyperlinks but it's not working.

Anyway, I'll wait to hear back from you before starting a new thread, to get your opinion.

Thanks again.
BV