Home

Hyperlink copy/paste

%3CLINGO-SUB%20id%3D%22lingo-sub-774312%22%20slang%3D%22en-US%22%3EHyperlink%20copy%2Fpaste%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-774312%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20having%20trouble%20copy%2Fpasting%20hyperlinks.%20For%20example%2C%20in%20the%20mock%20worksheet%20attached%2C%20I%20have%20supplier%20names%20listed%20in%20column%20C%20of%20sheet%201.%20In%20the%20real%20file.%20I%20have%20thousands%20of%20these%20supplier%20names%20in%20which%20I%20want%20to%20create%20hyperlinks%20that%20route%20to%20sheet2%20column%20B.%20My%20issue%20is%20that%20when%20I%20try%20to%20copy%20a%20hyperlink-%20say%20C3%20from%20sheet1%20down%20to%20C4%2C%20the%20friendly%20name%20on%20C4%20changes%20to%20the%20friendly%20name%20of%20C3.%20When%20pasting%2C%20I%20need%20each%20supplier%20name%20to%20remain%20their%20name%20and%20not%20transfer%20to%20another%20supplier%20name.%20My%20goal%20is%20that%20I%20need%20to%20be%20able%20to%20click%20and%20drag%20the%20hyperlinks%20on%20the%20supplier%20name%20column%20all%20the%20way%20down%20while%20the%20supplier%20names%20maintain%20their%20names.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20willing%20to%20try%20a%20macro%20(although%20not%20my%20preference%20bc%20I%20have%20little%20to%20no%20knowledge%20of%20macros).%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-774312%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-774785%22%20slang%3D%22en-US%22%3ERe%3A%20Hyperlink%20copy%2Fpaste%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-774785%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F382367%22%20target%3D%22_blank%22%3E%40nlw47%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20formula%20in%20Sheet!C2%2C%20copied%20down%20rows%2C%20is%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DHYPERLINK(%22%23Sheet2!B%22%26amp%3B%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EMATCH(LEFT(D2)%2CSheet2!B%3AB%2C0)%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ELEFT(D2))%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EPlease%20see%20the%20attached%20file%20and%20inform%20me%20if%20the%20foregoing%20formula%20returns%20your%20desired%20result.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-777571%22%20slang%3D%22en-US%22%3ERe%3A%20Hyperlink%20copy%2Fpaste%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-777571%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20so%20much%20for%20your%20reply%20and%20help!%3C%2FP%3E%3CP%3EWhen%20I%20copied%20your%20formula%20from%20the%20dummy%20sheet%20to%20the%20actual%2C%20I%20realized%20a%20minor%20issue.%20Column%20D%20are%20names%20or%20people%20(see%20excel%20sheet%20for%20example)%20and%20column%20C%20are%20names%20of%20companies.%20This%20is%20the%20issue%20because%20when%20I%20used%20your%20formula%20on%20the%20actual%20sheet%2C%20it%20returned%20with%3A%20%23NAME%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHopefully%20I'm%20making%20sense.%20Let%20me%20know%20if%20I%20need%20to%20explain%20further.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-778467%22%20slang%3D%22en-US%22%3ERe%3A%20Hyperlink%20copy%2Fpaste%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-778467%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F382367%22%20target%3D%22_blank%22%3E%40nlw47%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20the%20hyperlink%20to%20work%2C%20Column%20D%20of%20Sheet%201%20must%20match%20with%20Column%20B%20of%20Sheet%202.%20Thus%2C%20the%20formula%20in%20Sheet1!C2%2C%20copied%20down%20rows%2C%20is%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DHYPERLINK(%22%23Sheet2!B%22%26amp%3BMATCH(D2%2CSheet2!B%3AB%2C0)%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ED2)%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-778861%22%20slang%3D%22en-US%22%3ERe%3A%20Hyperlink%20copy%2Fpaste%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-778861%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20again%20for%20your%20help%20with%20this.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20sheet%201%2C%20columns%20C%20and%20D%20are%20different%20names.%20Column%20C%20is%20a%20company%20name%20whereas%20column%20D%20is%20a%20name%20of%20a%20person%20responsible%20for%20the%20engagement%20with%20that%20company.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOn%20sheet%202%2C%20column%20B%20is%20the%20same%20company%20name%20as%20sheet1%20column%20C.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20this%20changes%20things%20in%20the%20hyperlink%20formula.%3C%2FP%3E%3C%2FLINGO-BODY%3E
nlw47
New 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.

Related Conversations