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.
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!!
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
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 .
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!
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.
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.
Hi Leila, Here is a question in mind. Is there a way to parameterize a link to a cell in the same workbook? i.e. My first sheet = Main sheet to link to a cell = MySheet_2 in Main D1 = 'MySheet_2' is there a way to write E1= D1&!K3 instead of the normally typed =MySheet_2!K3
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?
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.
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
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.
Leila I thoroughly enjoy watching your TH-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?
@@LeilaGharani I try it out GetLink with name manager, it gives me only one link not all links in the workbook (I use Office365 not the insider version), how could i fix that?
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.
Is there a way to, not only capture the list of links, but use the list to update the domain. I need to do this for a migration. In SharePoint the domain will change after migration. There are alot of spreadsheets in a library that reference links to other excel files in the folder. I need to go through each file, find all the links, I'm thinking Queries and Connections, but I also need to change all the urls by replacing the previous domain with the new post-migration domain
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?
I've been searching TH-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
Thanks, cool trick. Just 2 questions for clarificaton: - You mentioned this command links at 2:46. Does this change in localized language versions? Your mentioning "links" is in the German Version the command for left, well because left translated to German would be links. So I'd imagine there is a language conflict, how if at all this could be resolved? - In 3:16 you speak of the insider edition. When will those commanders be widely accessible to the public? I thought they would be automatically updated to 365? What do I need to do to take advantage of these new commands? Thanks again for a great video!
ad 1) It's probably something like "Verknuepfung" or something like that. I don't have the German list. ad 2) Microsoft did not give any specific ETA for it. They will come to Office 365 automatically with an update as soon as Microsoft releases them. In the meantime you can check the pinned comment above for an alternate solution.
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.
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.
Hello Mam. Thankfully you solved my issue yesterday and I purchased the Excel course. But I had to ask you this now as it didn’t come across my mind previously that, does the paid excel tutorials have something different than already available free content from you. If yes, then how? Also as u upload new tutorials quite regularly, it won’t be a part of my purchased course. Yes? Because if that’s so isn’t it a little unfair I mean. Hopefully, I didn’t confuse you and made my queries clear. Kindly respond please. Thanks
The tutorials here are only a fraction of what is covered in the courses. So, the courses contain much more content and in a structured way. Hope this helps.
@@LeilaGharaniQuery solved! Thanks mam. One more thing for regular posting of the videos it will be done here and not in the course that I purchased. Like it has fixed designed content. Yes?
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.
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?
Thanks for this useful trick. One of the things that bothers me is breaking links - sometimes you have to trawl through a workbook endlessly to find references, which you need to delete completely, otherwise clicking 'Break Link' will not work. 😩
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...
th-cam.com/video/QTV7uZ5aOz4/w-d-xo.html
You teach literally things which saves time and life. Appreciate all your work 😀
I'm happy to hear that :)
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!
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!
Hi Leila, nice trick when translated... LINKS becomes LIAISONS in french. Works perfectly. Thanks
I didn't even know Excel 4.0 was a thing! You just opened up a new world with this. Thanks.
Great trick! Thank you! How can I locate the source cells that refer to these links?
All your videos are useful, love you videos !
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 .
Thanks for the details on Links in excel. Really helpful.
Glad it was helpful!
👍 Awesome trick Leila, it is very helpful. Could it be crated as a private function instead as a name?
Nice easy way to improve functionality, thanks for the share!
As I said before u’re a genius
Just what I needed, spot on! Thank you, it worked like a charm.
I like this. Those external links drive me mad. Thanks Leila. 👍
I know, same here. Always makes me uncomfortable :)
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 :)
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!
Thank you Leila you are a life saver and a fantastic teacher. Always something new to learn.
You are so welcome!
Once again you have given some thing new to learn thanks a lot Leila. Keep it coming
I will do my best :)
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!! :-)
Absolutely loving this trick
Excel features always amaze me.
There's always something new to learn.
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.
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 :)
Thanks LG for another cool trick
Thanks to Bob for coming up with this genius solution.
Wow it’s super useful!! Thanks Leila for sharing your knowledge!!
Neat-O! Thanks Leila and Bob for the tips!
Our pleasure Doug :)
You never fail to amaze me. 😊
That's good to hear :)
Thank you so much for sharing the tricks. It is really useful.
This is super helpful but for some reason it's only giving me one link. any idea how to fix?
Supper explanation and very helpful
Impressive !! You're absolutely correct - there is always so much to learn in Excel. Great trick, appreciated.
Hi Leila,
Here is a question in mind. Is there a way to parameterize a link to a cell in the same workbook?
i.e. My first sheet = Main
sheet to link to a cell = MySheet_2
in Main D1 = 'MySheet_2' is there a way to write E1= D1&!K3 instead of the normally typed =MySheet_2!K3
This was I searching for a long time.. Love you LG...
Excellent video. You are great. Thank you so much MM.
Our pleasure. Glad you like it.
Very impressive!!
TH-cam should offer something more than thumb button for a content like yours. you are a magician.
Thank you but I'm just delivering it. Bob came up with this magic :)
@@LeilaGharani I'am talking about All your content not only this magic video.
deliver my thanks to Bob.
thank you
@@mswordexpert Your thanks are received. Thanks for the thanks
Is there a way to get it to tell you what cells have the external links?
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?
Can we select multiple random Cells on sheet number 1 and link on sheet number 2 randomly once???
Too good....great work. I keep learning
Glad to hear that :)
Nice! I do, too!
Will always 👍 your vids Leila!
Thank you for liking and supporting :)
Amazing! thank you!!
Beautiful and smart!
Thank you!
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.
Great trick!
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.
Dear Mam, how can we find out the reference cell Number where is the placed the external Links
Please help
Brilliant! And so useful
Glad to hear that Roberto. Thanks to Bob's great find.
Good stuff. Thanks for sharing this.
Great video! Leila
You saved my day!
Does this work for web links? I did everything excatly as you and I get an #NAME? or #N/A error... how to fix this?
Thanks so much
Thanks Leila,
good trick
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...
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.
Hi. Have you got any tips for using the maps? Can you report on multiple sales data per province/country?
Brilliant! thank you very much Leila!
My pleasure Cristian.
Much needed tutorial 😊👍
can I Hyperlink those links?
Thanks 😊
Hi Leila, please can you please make video on Pivot through VBA?
Awesome
Hummm. Did not work for me. I kept getting the #NAME error
Leila I thoroughly enjoy watching your TH-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?
Wow! Cool tip thanks
Glad you like it Chris.
Also glad you liked it!
Wunderbar Liebe Leila, Viele Liebe Grüsse 🤗 💞
Hallo Katerina :)
Can you advice how to list all formulas on the spreadsheet
THANKS 😊! IT'S very useful!
I'm glad to hear that.
Wow, this is mind blowing thanks for sharing 👍
My pleasure Stephen. It's really a greating finding by Bob.
Thanks!
I used this before, didn't know its excel 4.0...
Thanks for information
Nice and useful leila :-)
Glad you like it Mohamed.
@@LeilaGharani I try it out GetLink with name manager, it gives me only one link not all links in the workbook (I use Office365 not the insider version), how could i fix that?
@@mohamedchakroun4973 As it says in the video, use =INDEX(Getlink,ROW(A1)) and fill down
@@bobumlas6562 Thanks a lot, I didn't catch it from the video :-) Sorry :-)
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.
Is it possible to get the links from a cloud directory, like one drive?
Is there a way to, not only capture the list of links, but use the list to update the domain. I need to do this for a migration. In SharePoint the domain will change after migration. There are alot of spreadsheets in a library that reference links to other excel files in the folder. I need to go through each file, find all the links, I'm thinking Queries and Connections, but I also need to change all the urls by replacing the previous domain with the new post-migration domain
Is there equivalent function or shortcut use in excel 2016? Without using VBA code? Thanks
thanks
hello, Leila, this one is a very nice trick I am tried but not getting the proper list so, I am stuck here. help! me.
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?
Can you get this to work without the dynamic array for those of us without the insider edition?
Yes - you can use this formula instead: =INDEX(getlinks,ROW(A1)) and then drag down. To avoid the #REF error, wrap it inside the IFERROR function.
@@LeilaGharani Thanks Leila! :)
I've been searching TH-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
Thanks, cool trick.
Just 2 questions for clarificaton:
- You mentioned this command links at 2:46. Does this change in localized language versions? Your mentioning "links" is in the German Version the command for left, well because left translated to German would be links. So I'd imagine there is a language conflict, how if at all this could be resolved?
- In 3:16 you speak of the insider edition. When will those commanders be widely accessible to the public? I thought they would be automatically updated to 365? What do I need to do to take advantage of these new commands?
Thanks again for a great video!
ad 1) It's probably something like "Verknuepfung" or something like that. I don't have the German list.
ad 2) Microsoft did not give any specific ETA for it. They will come to Office 365 automatically with an update as soon as Microsoft releases them. In the meantime you can check the pinned comment above for an alternate solution.
Same here for the Dutch version of Excel. These functions are not on the translations list, so I have no idea how to make it work.
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.
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.
Hello Mam. Thankfully you solved my issue yesterday and I purchased the Excel course. But I had to ask you this now as it didn’t come across my mind previously that, does the paid excel tutorials have something different than already available free content from you. If yes, then how? Also as u upload new tutorials quite regularly, it won’t be a part of my purchased course. Yes? Because if that’s so isn’t it a little unfair I mean. Hopefully, I didn’t confuse you and made my queries clear. Kindly respond please. Thanks
The tutorials here are only a fraction of what is covered in the courses. So, the courses contain much more content and in a structured way. Hope this helps.
@@LeilaGharaniQuery solved! Thanks mam. One more thing for regular posting of the videos it will be done here and not in the course that I purchased. Like it has fixed designed content. Yes?
Can you use Excel 4.0 macros to get one or multiple values from a closed workbook?
Is there a way to do this on a Mac?
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.
thanks...
Hello
Vlookup formula se other se link kaise kar sakate hai
Plz rply
HaPPy Diwali Leila 🙏
Great Staff : ) : )
WOW!
Yep. It's a great finding from Bob!
Glad you like it!
Can you advice how to list links with cell
Thank you so much
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?
Thanks for this useful trick. One of the things that bothers me is breaking links - sometimes you have to trawl through a workbook endlessly to find references, which you need to delete completely, otherwise clicking 'Break Link' will not work. 😩
That has happened to me before! Very annoying.