Hyperlink copy/paste

Copper Contributor

I am having trouble copy/pasting hyperlinks. For example, in the mock worksheet attached, I have supplier names listed in column C of sheet 1. In the real file. I have thousands of these supplier names in which I want to create hyperlinks that route to sheet2 column B. My issue is that when I try to copy a hyperlink- say C3 from sheet1 down to C4, the friendly name on C4 changes to the friendly name of C3. When pasting, I need each supplier name to remain their name and not transfer to another supplier name. My goal is that I need to be able to click and drag the hyperlinks on the supplier name column all the way down while the supplier names maintain their names. 

I am willing to try a macro (although not my preference bc I have little to no knowledge of macros). 

4 Replies

@nlw47 

The formula in Sheet!C2, copied down rows, is: 

=HYPERLINK("#Sheet2!B"&
MATCH(LEFT(D2),Sheet2!B:B,0),
LEFT(D2))

Please see the attached file and inform me if the foregoing formula returns your desired result.

@Twifoo 

 

Thank you so much for your reply and help!

When I copied your formula from the dummy sheet to the actual, I realized a minor issue. Column D are names or people (see excel sheet for example) and column C are names of companies. This is the issue because when I used your formula on the actual sheet, it returned with: #NAME?

 

Hopefully I'm making sense. Let me know if I need to explain further. 

 

@nlw47 

For the hyperlink to work, Column D of Sheet 1 must match with Column B of Sheet 2. Thus, the formula in Sheet1!C2, copied down rows, is: 

=HYPERLINK("#Sheet2!B"&MATCH(D2,Sheet2!B:B,0),
D2)

@Twifoo 

 

Thank you again for your help with this. 

 

For sheet 1, columns C and D are different names. Column C is a company name whereas column D is a name of a person responsible for the engagement with that company. 

 

On sheet 2, column B is the same company name as sheet1 column C. 

 

I think this changes things in the hyperlink formula.