Get ALL External Links with This SIMPLE Excel TRICK (as NEVER seen before!)
Вставка
- Опубліковано 23 жов 2019
- Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
With this Excel trick you can use a formula to show all the external links in your Excel workbook in one place. This way you can keep an eye on where the data is coming from and if any link is pointing to the wrong workbook.
With this Excel hack you can also write a formula to get the list of files from a specific directory and bring it to your Excel sheet. So if you're waiting for your colleague to add a file to a directory, you'll be able to see it in your Excel formula results once you refresh the list.
The fun part about this Excel trick is it combines 4.0 Excel Macros with Excel dynamic arrays. You can use the Excel Transpose function to get a vertical list of external links for file names.
🔑 Key Points:
- Track External Links: Learn a swift method to list all external links in a workbook, bypassing the traditional step-by-step approach.
- Dynamic Array Spill Feature: Utilize Excel's latest calculation engine to get a spilled range of links, displaying them horizontally or vertically using the transpose function.
- Creating Custom Excel 4.0 Macros: Discover how to create names for Excel 4.0 macros to perform specific functions, such as 'Get Links' and 'Get Files'.
- File Directory Listing: See how to generate a list of all file names in a specified directory, using wildcards and dynamic arrays.
- Sorting and Filtering Data: Explore the use of the SORT function and file extensions to organize and filter the list of files.
- Saving Files with Macros: Understand the importance of saving files with Excel 4.0 macros as an XLSM file to retain new functionalities.
⯆ Blog post is here: www.xelplus.com/excel-externa...
Bob's Excel Magic book on Amazon: amzn.to/2oVFRln
All about Excel's NEW Dynamic arrays - check out the complete playlist: • Excel for Office 365 &...
★ My Online Excel Courses ► www.xelplus.com/courses/
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#excel
Stay ahead with our Weekly Newsletter. Get the latest insights and updates straight to your inbox 👉 link.xelplus.com/yt-c-newsletter
Update 2020: The new functions are available in Excel for Office 365. For those that don't have Dynamic Arrays - use this formula instead: =INDEX(getlinks,ROW(A1)) and then drag down. To avoid the #REF error, wrap it inside the IFERROR function.
I love you Leila!
You are right MM.
Thanks Leila
Awesome!
This option should be the main idea in the video. Dynamic arrays is not available for everyone yet
Thanks for the Bob-Inspired Macro Functions!! I agree, we NEVER stop learn and finding new tricks. Fun : ) But I disagree with Help when it says we should migrate away from Excel 4 Macro Functions - there are still so many uses for some of those functions!!
Agree : ) I love Bob's out of the box thinking. The great thing about these functions is they are easy to use...
ua-cam.com/video/QTV7uZ5aOz4/v-deo.html
You teach literally things which saves time and life. Appreciate all your work 😀
I'm happy to hear that :)
Impressive !! You're absolutely correct - there is always so much to learn in Excel. Great trick, appreciated.
Hi Leila.. what an incredibly clever set of tips. Thanks for sharing them. For external links, I have always used the Find feature searching on ".xl" and Within Workbook.. then CTRL+A to highlight.. or Edit Links through the ribbon menu, as you demonstrated. Getting the list right on the worksheet is great. And, the GetFiles trick is super helpful. For those using legacy EXCEL, wrap your named functions (GetLinks or GetFiles) in TRANSPOSE and just remember to highlight a range of cells first and press CSE to mimic the spilling behavior vertically in the range. If you highlight a greater range than needed, you can use Conditional Formatting to hide the #N/A errors in the larger range that you have defined to handle future additions to the list (note that IFERROR and IFNA don't seem to work with TRANSPOSE, nor can you trap with standard IF and ISNA). Not as elegant as in new dynamic array EXCEL, but gets the job done. Thanks again for these innovative and hidden tips.. always something new and interesting coming from LeilaG :)) - Thumbs up!
PS - You could also get there using VBA either as a SUB or a UDF, as in:
Sub ListLinks()
Dim aLinks As Variant
aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
Dim i As Integer
If Not IsEmpty(aLinks) Then
Sheets.Add
For i = 1 To UBound(aLinks)
Cells(i, 1).Value = aLinks(i)
Next i
End If
End Sub
Thank you Wayne for sharing the additional tips in finding links. Also the VBA to get the job done. Love it!
Amazingly helpful trick! Seriously, you guys are more than experts. You invent new uses of excel. Keep it up. Thank you so much!
Glad you like our little trick :)
Wow it’s super useful!! Thanks Leila for sharing your knowledge!!
I didn't even know Excel 4.0 was a thing! You just opened up a new world with this. Thanks.
Thank you Leila you are a life saver and a fantastic teacher. Always something new to learn.
You are so welcome!
Just what I needed, spot on! Thank you, it worked like a charm.
This was I searching for a long time.. Love you LG...
All your videos are useful, love you videos !
Thank you so much for sharing the tricks. It is really useful.
Nice easy way to improve functionality, thanks for the share!
As you put on the title, "never seen before"! Really helpful, thanks Leila. Now I can have these vexing links in my Excel workbooks under control.
Exactly! Good to have things under control :)
Absolutely loving this trick
Hi Leila, nice trick when translated... LINKS becomes LIAISONS in french. Works perfectly. Thanks
Great trick! Thank you! How can I locate the source cells that refer to these links?
Thanks for the details on Links in excel. Really helpful.
Glad it was helpful!
Once again you have given some thing new to learn thanks a lot Leila. Keep it coming
I will do my best :)
Much needed tutorial 😊👍
Hi Leila thanks for your valuable teaching and information. Is there any ways to compare data between 2 sheets against a particular column value and mention the difference results in conditional formatted cell.
For eg: In Sheet 1 and Sheet 2, Column A has the staff No and from B to Z have other basic info of employees. How to spot the difference in Sheet 1 against Sheet 2 using conditional formatting
I like this. Those external links drive me mad. Thanks Leila. 👍
I know, same here. Always makes me uncomfortable :)
HI Leila, THANK YOU THANK YOU, I this is amazing! I kind of hated Edit link box...can't expand to see the full path or get the the long file names. So much to learn every day in Excel even though we have all become experts by watching your videos! lol again Thanks you! keep up the wonderful work you do and teach us!
Leila first of all you are unbelievable beautifully, second thing you are amazing your explanation is so cool and understandably , thank you wery much , you are again open for me another world .
You never fail to amaze me. 😊
That's good to hear :)
Thanks Leila,
good trick
Excellent video. You are great. Thank you so much MM.
Our pleasure. Glad you like it.
Neat-O! Thanks Leila and Bob for the tips!
Our pleasure Doug :)
Great trick!
Good stuff. Thanks for sharing this.
Great video! Leila
Amazing! thank you!!
Brilliant! thank you very much Leila!
My pleasure Cristian.
Very impressive!!
Thanks for video!
I used this before, didn't know its excel 4.0...
Thanks for information
Supper explanation and very helpful
You saved my day!
Brilliant! And so useful
Glad to hear that Roberto. Thanks to Bob's great find.
This trick saved me so much time. Another person was explaining to write code in VBA whereas this did it within seconds
Glad it was helpful!
👍 Awesome trick Leila, it is very helpful. Could it be crated as a private function instead as a name?
Thanks so much
Hi. Have you got any tips for using the maps? Can you report on multiple sales data per province/country?
Thanks LG for another cool trick
Thanks to Bob for coming up with this genius solution.
Will always 👍 your vids Leila!
Thank you for liking and supporting :)
As I said before u’re a genius
Thank you for this video. I am still struggling with something though - how do I determine the link for a Picture that I have inserted into a cell, that was inserted via Insert | Pictures, and then inserted as 'Link to File'. Those links don't show up using this method. How would I get such a link?
Excel features always amaze me.
There's always something new to learn.
Beautiful and smart!
Thank you!
THANKS 😊! IT'S very useful!
I'm glad to hear that.
Thanks 😊
Great Staff : ) : )
Thank you so much
Hi Leila,
Your videos are very innovative & always new things to learn.
Can you help me solving how to identify all links together when we press ctrl+[ to go that specific cell but it goes to only 1 cell. Is there any shortcut tht can help me idendify all the cells that are linked from.
Too good....great work. I keep learning
Glad to hear that :)
Nice! I do, too!
Wow, this is mind blowing thanks for sharing 👍
My pleasure Stephen. It's really a greating finding by Bob.
Thanks!
Can you use Excel 4.0 macros to get one or multiple values from a closed workbook?
I was wondering if there is a way to use the get files in directory for writing a macro. So that you get maybe a specific page of the listed files.
Hello Leila, but how I can find those links in my workbook?I mean in specific cells.
Awesome
Is it possible to use GetFiles on a Sharepoint folder that is not mapped as a drive? Also wondering if other file attributes could be obtained, like last modified date and time for example?
Wow, Leila! As I am a fan of you checking most of the videos you uploaded, it's progressively improving not only the presentation but also your make up... Thumbs upppppp Nice profile IG
Doing my best :) Glad you like it.
Will it show hidden and temp files? Example if I need to cleanup network folder and want a list of all files in root and sub folders. Layered folder approach. And then incorporate the date last changed. For maintenance.
Wow! Cool tip thanks
Glad you like it Chris.
Also glad you liked it!
Hi, do you know how to create a macro for sequenece in excel versions which do not have inbuilt sequence formula
That's great, I m facing an issue related to link. If i am keeping all linked files to one folder and then later copy the whole folder some where else, some of the files links moved to newly copied files but some remain linked to old location, not able to figure out this. Would you please assist.
Leila I thoroughly enjoy watching your UA-cam videos on Excel. I have a question that I cannot find the answer to. I have a stubborn external link that no longer exist. I tried breaking the link but it won't let me. Do you have any recommendations?
Lov your vids but How can you specify just the price value for example from that dynamic table?I want to be able to specify one single specific value i.e. stock price not the whole table to be automatically updated ... I cant seem to find any filters for that, do you know how?
Hi. Great tips thanks. Unfortunately my GetFiles didn't quite work, it only lists one file out of 20 odd in the folder. Do you know what else I need to do please? Thank you.
thanks
Hi Leila, is it possible to move an excel file with links on my desktop to one drive without breaking the file links? Some links referred are in my documents folder.
I like the way she says "This one" in that first second.
thanks...
Is there equivalent function or shortcut use in excel 2016? Without using VBA code? Thanks
can I Hyperlink those links?
Ma'am
I have one question
One of my friend sent me the file which has external link and I want to download that external file
Is it possible?
Pls help
Hi Leila, please can you please make video on Pivot through VBA?
Hello
Vlookup formula se other se link kaise kar sakate hai
Plz rply
this is the kind of stuff that would driven me cuckoo bananas when taking over someone else's workbook. I would have never in a million years imagined you could reference a built-in macro like this.
It really is genius.
@@LeilaGharani Thanks!! :-)
Thank you for this video! My Edit Links is greyed out. First I got a message saying I had to save the workbook as a macro enabled workbook so I did but no joy on finding the links or getting the formula to work. On another worksheet I have about 100 links to emails and I have each one in a text box so I can have multiple links in a single cell. I haven’t tried this on my home computer or Excel online yet, maybe it works better there.
Nice.
How to paste special a formula when in filter mode?
Please help
I have Excel workbook file in that around 40 plus sheets or tabs and around 40 plus external links. I want to know in which tab or those external links used as formula can you help me in this.
Can we select multiple random Cells on sheet number 1 and link on sheet number 2 randomly once???
Can you advice how to list all formulas on the spreadsheet
a problem i am facing often is broken links inside data validation list reference. is there a way to display those?
How do i tell which cells of linked spreadsheet has been updated? IS there anyway to highlight or track when changes have been made and i refresh Data all?
I know you can go into individual spreadsheet to Trace, but i have many worksheets linked to Masterfile and have hard time tracking which are updated or not.
Wunderbar Liebe Leila, Viele Liebe Grüsse 🤗 💞
Hallo Katerina :)
I have a few excel workbooks to go through to find their links. Is there a way to find all links for all excel files without going into each one of them to type getlinks? Also is there a way to count how many external links to each external file?
Is it possible to get the links from a cloud directory, like one drive?
I've been searching UA-cam videos on how to export the sheet to save as another workbook using VBA but it should be already breakLinks so that if there mighty be an specific alteration for the exported new sheet as workbook it would be easy to deal with it. Can you help me how? thank you
This is super helpful but for some reason it's only giving me one link. any idea how to fix?
Can you advice how to list links with cell
This is mind blowing, very useful to pull data from that particular files using cell address formula in nest...
But it's showing only 1st file from given directory. How to fix this?
I am using MS MS office 2019
Another great video Leila. What is the name =Links() for the german version of Excel? Have a great evening. Greetings from Germany
Same question, but in portuguese...
HaPPy Diwali Leila 🙏
Leila, this is amazing.
What are the other excel 4.0 functions which can similarly be used in a macro enabled excel sheet xlsm without using VBA code?
You can buy the book and look at page 92!! (Available on Amazon) - "This isn't Excel, it's magic"
@@bobumlas6562 Thanks Bob. It indeed is magic.
@@Up4Excel Sure. I'll wait for that.
Is there a way to get it to tell you what cells have the external links?