Links to changing file names

%3CLINGO-SUB%20id%3D%22lingo-sub-1539379%22%20slang%3D%22en-US%22%3ELinks%20to%20changing%20file%20names%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1539379%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20create%20a%20simple%20way%20to%20have%20a%20link%20within%20a%20spreadsheet%20where%20I%20%22enter%22%20the%20linked%20spreadsheet%20name%20and%20location%20in%201%20location%20-%20Here%20is%20the%20scenario......%20%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20file%20called%20June%20sales.xlsx%20that%20I%20want%20to%20graph%26nbsp%3B%20the%20information%20in%20cells%20b1-b10.%20However%20this%20is%20a%20massive%20spreadsheet%20so%20I%20want%20to%20create%20another%20simple%20spreadsheet%20that%20will%20grab%20those%20values%20and%20graph%20them.%20The%20problem%20is%20that%20during%20the%20month%20I%20change%20the%20file%20name%20-%20for%20example%26nbsp%3B%20%22June%20Sales%20V1.xlsx%22%20then%20%22June%20sales%20V2.xlsx%22%26nbsp%3B%20etc.%20Now%20I%20can%20go%20into%20the%20spreadsheet%20with%20the%20links%20and%20go%20through%20the%20tedious%20process%20of%20editing%20each%20of%20the%20links%2C%20but%20what%20I%20was%20hoping%20for%20was%20a%20simple%20way%20that%20I%20change%20the%20filename%20in%20one%20place%20(a%20single%20cell)%20and%20have%20it%20update%20all%20links.%20Any%20ideas%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1539379%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-1539522%22%20slang%3D%22en-US%22%3ERe%3A%20Links%20to%20changing%20file%20names%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1539522%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F736849%22%20target%3D%22_blank%22%3E%40brad_tay64%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%3CP%3EYou%20can%20play%20with%20this%20a%20little%20to%20adjust%20it%20to%20your%20needs.%20In%20the%20screenshot%20attached%2C%20if%20I%20change%20the%20filename%20to%20%22sourceV2%22%20in%20explorer%20and%20in%20cell%20D1%20my%20value%20in%20cell%20A1%20will%20pick%20up%20the%20new%20workbook.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Bennadeau_0-1595449528047.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F207277i3D6E2DA971631718%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Bennadeau_0-1595449528047.png%22%20alt%3D%22Bennadeau_0-1595449528047.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1548210%22%20slang%3D%22en-US%22%3ERe%3A%20Links%20to%20changing%20file%20names%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1548210%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F703724%22%20target%3D%22_blank%22%3E%40Bennadeau%3C%2FA%3E%26nbsp%3B%20Thank%20you%20-%20works%20like%20a%20charm.%20The%20only%20thing%20you%20need%20to%20make%20sure%20of%20is%20that%20the%20file%20you%20are%20linking%20to%20is%20open.%20I%20appreciate%20your%20quick%20response.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I am trying to create a simple way to have a link within a spreadsheet where I "enter" the linked spreadsheet name and location in 1 location - Here is the scenario......  

I have a file called June sales.xlsx that I want to graph  the information in cells b1-b10. However this is a massive spreadsheet so I want to create another simple spreadsheet that will grab those values and graph them. The problem is that during the month I change the file name - for example  "June Sales V1.xlsx" then "June sales V2.xlsx"  etc. Now I can go into the spreadsheet with the links and go through the tedious process of editing each of the links, but what I was hoping for was a simple way that I change the filename in one place (a single cell) and have it update all links. Any ideas? 

2 Replies
Highlighted

Hi @brad_tay64 ,

You can play with this a little to adjust it to your needs. In the screenshot attached, if I change the filename to "sourceV2" in explorer and in cell D1 my value in cell A1 will pick up the new workbook.

Bennadeau_0-1595449528047.png

 

Highlighted

@Bennadeau  Thank you - works like a charm. The only thing you need to make sure of is that the file you are linking to is open. I appreciate your quick response.