Auto populate Hyperlinks in by dragging down a cell in office 365

%3CLINGO-SUB%20id%3D%22lingo-sub-1085185%22%20slang%3D%22en-US%22%3EAuto%20populate%20Hyperlinks%20in%20by%20dragging%20down%20a%20cell%20in%20office%20365%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1085185%22%20slang%3D%22en-US%22%3E%3CP%3EI%20want%20to%20drag%20a%20cell%20down%20auto%20populating%20each%20next%20cell.%20%26nbsp%3BEx....%20MJN1001%20through%20MJN1100...%20no%20problem%2C%20excels%20will%20allow%20this%20by%20the%20copy%20feature.%20%26nbsp%3BEach%20cell%20needs%20to%20hyperlink%20into%20a%20folder%20that%20matches%20each%20number%20MJN1001%20through%20MJN1100.%20%26nbsp%3BIf%20I%20set%20the%20first%20cell%20to%20MJN1001%2C%20the%20next%20cell%20becomes%20MJN1002%2C%20however%2C%20the%20hyperlink%20is%20still%20MJN1001.%20%26nbsp%3BHow%20do%20I%20get%20the%20hyperlink%20to%20auto%20populate%2C%20as%20I%20drag%20it%20down%20the%20column%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1085185%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1085508%22%20slang%3D%22en-US%22%3ERe%3A%20Auto%20populate%20Hyperlinks%20in%20by%20dragging%20down%20a%20cell%20in%20office%20365%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1085508%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F380356%22%20target%3D%22_blank%22%3E%40Budman36%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20your%20first%20hyperlink%20is%20in%20cell%20A2%20that%20could%20be%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DHYPERLINK(%22file%3A%2F%2Fc%3A%5CMJN%22%20%26amp%3B%20(1000%2BROW()-ROW(%24A%241)))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1090850%22%20slang%3D%22en-US%22%3ERe%3A%20Auto%20populate%20Hyperlinks%20in%20by%20dragging%20down%20a%20cell%20in%20office%20365%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1090850%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHello%20Sergei%2C%3C%2FP%3E%3CP%3EYour%20idea%20worked...%20part%20way.%20%26nbsp%3BI%20played%20with%20this%2C%20and%20got%20the%20hyperlink%20too%20populate%2C%20but%20it%20won't%20open%20the%20files.%20%26nbsp%3B%20The%20files%20are%20stored%20on%20a%20OneDrive.%20%26nbsp%3BWould%20that%20matter%3F%20%26nbsp%3B%20Here%20is%20an%20example%20of%20what%20I%20did.%20%26nbsp%3BI%20changed%20the%20directory%20names%20for%20security%20reasons%2C%20but%20the%20idea%20is%20the%20same.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DHYPERLINK(%22C%3A%5CUsers%5CJohn%20Smith%5COneDirve-ABC%20Company.%20INC%5CABC%20Folder%5C%22%26amp%3B'Formula%20Page'!B107%26amp%3B%22%22%2CFormula%20Page'!B107)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20hidden%20page%20on%20the%20ABC%20Folder%20that%20I%20call%20%22Formula%20Page%22.%20%26nbsp%3BIn%20this%20page%20I%20have%20all%20my%20folder%20numbers%20listed%2C%20so%20it%20displays%20the%20number%20on%20my%20main%20page%20vs.%20the%20hyperlink%20code.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1090876%22%20slang%3D%22en-US%22%3ERe%3A%20Auto%20populate%20Hyperlinks%20in%20by%20dragging%20down%20a%20cell%20in%20office%20365%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1090876%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F380356%22%20target%3D%22_blank%22%3E%40Budman36%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20shall%20work%20with%20synced%20OneDrive%20folder.%20I%20played%20with%20my%20one%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DHYPERLINK(%22C%3A%5CUsers%5CSergei%5COneDrive%20-%20ABC%20Inc%5C%22%20%26amp%3B%20'Formula%20Page'!B8%2C'Formula%20Page'!B8)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eit%20opens%20the%20folder%20named%20in%26nbsp%3B'Formula%20Page'!B8%20(e.g.%20%3CSTRONG%3EDocuments%3C%2FSTRONG%3E)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1090917%22%20slang%3D%22en-US%22%3ERe%3A%20Auto%20populate%20Hyperlinks%20in%20by%20dragging%20down%20a%20cell%20in%20office%20365%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1090917%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you.%20%26nbsp%3BI%20realized%20why%20it%20wasn't%20working%20when%20I%20saw%20your%20code.%20%26nbsp%3BI%20had%20the%20wrong%20file%20directory.%20%26nbsp%3B%20Sometimes%20a%20fresh%20look%20gets%20you%20to%20see%20what%20is%20in%20front%20of%20you.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1091022%22%20slang%3D%22en-US%22%3ERe%3A%20Auto%20populate%20Hyperlinks%20in%20by%20dragging%20down%20a%20cell%20in%20office%20365%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1091022%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F380356%22%20target%3D%22_blank%22%3E%40Budman36%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%2C%20glad%20to%20help%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Contributor

I want to drag a cell down auto populating each next cell.  Ex.... MJN1001 through MJN1100... no problem, excels will allow this by the copy feature.  Each cell needs to hyperlink into a folder that matches each number MJN1001 through MJN1100.  If I set the first cell to MJN1001, the next cell becomes MJN1002, however, the hyperlink is still MJN1001.  How do I get the hyperlink to auto populate, as I drag it down the column?

5 Replies
Highlighted

@Budman36 

If your first hyperlink is in cell A2 that could be like

=HYPERLINK("file://c:\MJN" & (1000+ROW()-ROW($A$1)))

 

Highlighted

@Sergei Baklan 

Hello Sergei,

Your idea worked... part way.  I played with this, and got the hyperlink too populate, but it won't open the files.   The files are stored on a OneDrive.  Would that matter?   Here is an example of what I did.  I changed the directory names for security reasons, but the idea is the same. 

 

=HYPERLINK("C:\Users\John Smith\OneDirve-ABC Company. INC\ABC Folder\"&'Formula Page'!B107&"",Formula Page'!B107)

 

I have a hidden page on the ABC Folder that I call "Formula Page".  In this page I have all my folder numbers listed, so it displays the number on my main page vs. the hyperlink code. 

Highlighted

@Budman36 

It shall work with synced OneDrive folder. I played with my one:

=HYPERLINK("C:\Users\Sergei\OneDrive - ABC Inc\" & 'Formula Page'!B8,'Formula Page'!B8)

it opens the folder named in 'Formula Page'!B8 (e.g. Documents)

Highlighted

@Sergei Baklan 

Thank you.  I realized why it wasn't working when I saw your code.  I had the wrong file directory.   Sometimes a fresh look gets you to see what is in front of you. 

 

thank you!

Highlighted

@Budman36 , you are welcome, glad to help