4 Tricks for Linking Workbooks in Excel
ฝัง
- เผยแพร่เมื่อ 13 ก.ย. 2024
- MY FULL EXCEL VBA COURSE [35% Discount]: www.teachexcel...
35% Discount runs until Jan. 26, 2022
Excel File: www.teachexcel...
Ask a Question: www.teachexcel...
4 easy to use tricks to link data between multiple workbooks in Excel.
This includes linking cells, ranges, formulas, and functions; as well it shows you how to consolidate data from multiple workbooks and how to use the power query feature to import and link data in a more powerful way between multiple spreadsheets.
TeachExcel.com
MY FULL EXCEL VBA COURSE [35% Discount]: www.teachexcel.com/premium-courses/79/excel-vba-course-beginner-to-expert?src=yt_pinned_comment_RzvMhoZUaCg
35% Discount runs until Jan. 26, 2022
Your tutorial on linking workbooks was very helpful in that I discovered that there are more efficient and powerful ways to take advantage of MS Excel. I have been doing so much of the analysis and linking work manually, between cells. Thank you so much and the timber of your voice is excellent for teaching, clear and calm!! Kudos
Thank so much! Amazing tutorial. Would love to learn advanced power query! Thanks again!
Thank you,
It's a clear and concise explanation easy to comprehend.
Thank You so much! I was looking for something like this so much easier :)
OHMYGOD!! THIS HELPED ME SO MUCH!! THANK YOU!!!
You have a nice presentational style. Easy to follow along. Thank You. Would you know how to handle the situation where your source worksheet has new rows added to them? If you are pulling in totals from a particular row, it's all good until new rows are added and change the row numbers on you. Is there a way to roll with that sort of change?
This is awesome. Thank you. I have a question.
Is there a way to freeze/save the data in a workbook that is from another workbook so that I can email an excel report?
My problem: i need to send an excel report by email. The reciever won’t have access to all of my workbooks.
Does that make sense?
Anyway, thank you again. This tutorial was very concise!!!🙏😊
I am so glad I have discovered you Channel !! Many thanks !! I guess the only reason you have few subscriptions is because 2016 is yet to catch !!! That said great stuff, Congratulations !!!
I'm so glad to help! And I'm more than happy for you to help grow the channel if you can :)
Hey guys! So I am using linking to connect a master sheet to a dashboard, but the formatting that my team used on the master sheet isn’t very uniform, and has lots of spaces. This means there would be a lot of zeros to delete.
A trick that you can use to get away from all this deleting, is to add an IF() wrapper:
=IF(‘[MyWorkbook.xls]Sheet1’!A1 “”, ‘[MyWorkbook.xls]Sheet1’A1, “”)
The if() wrapper checks to make sure that the cell being referenced isn’t blank. If it isn’t blank, then it will show the normal reference. If it is blank, then it will show the cell as blank.
baby its meee ur long lost mommyyy
@@louzagouta9206 什么鬼？
Very nice and clear tutorial!! Thank you, Sir!
Thank you
That's wanderful sir.thanks alot.
This was very helpful
Is it possible to link entire tables to eachother in a more efficient way ?
lUV YOU SIRJI
maybe i have to dive deep to understand this high class knowledge. :*
very useful tutorial. I request u to give tutorials about solver tool in excel.
Is there any way to import the data from a different workbook and keep the formatting? (rather than the data coming in as a table)
thnx for this this save a lot of time for me 👍👍👍
you're the best!
Thank you so much.
Great work and great explain
Thank you!!!
hi! Great, amazing tutorial. I have a problem though. WOrking on MacOS, using Excel, don't seem to have the option of importing anything other than Databases, text files... How could I add linking files, that option seems awesome?
I don't see that too as I'm using Mac , is that hidden anywhere else?
These are amazing tips but unfortunately do not work with Google Sheets.
GREAT, thanks....
Superb!!!
Thanks
I have one question that I want to link 2 sheets and if I add row in one sheet then in the second sheet it should automatically get added.is it possible??
If I have a lot of files called [4 tricks for linking workbooks in Excel - 2.xlsx] - 3.xlsx, - 4.xlsx..., how can I edit that number 2 (... - 2.xlsx) of the path sequentially?
I don't want to do it manually
Do you know how to connect data in the linked sheet to a new column? When I deleted a row from the original sheet, the data of the row which is in the linked sheet is also deleted but not the all row. Is this something can't be done in linked sheets. I hope I make myself clear. Please help me if you know
Hi there. Great video! One question I have though, what happens when you move your files into the likes of SharePoint or teams or OneDrive? Will excel ‘detect’ the new location and update accordingly?
This sounds like it won't work well, but test it out with your version of Excel and see how it goes. A lot depends on the specifics of your setup.
Very helpful, thank you
Thanks for this great tutorial. Let's say I'm using two workbooks A & B, A consumes data from B as per the link reference method (formulas in your example). All works fine. Data is updated in B and when I open A it asks whether I want to refresh data and saying yes does what it says.
The challenge I run into is the folder structure. Both files A & B sit on a shared Dropbox folder - a Dropbox folder as you may know looks like any other folder in Windows Explorer and updating a what appears to be a "local" file then updates the Dropbox copy which then syncs across all participants.
When another user (connected to the same folder via Dropbox) opens the file A and tries to update the data, Excel can't find B since the absolute path to B is different to the absolute path to B from my computer.
After having seen your tutorial do you think the Get Data functionality in Excel would allow me to get around this issue?
have you found a workaround for this yet? cuz this is the exact problem I have been trying to figure out for a couple days now
@@isaacebling3310 YEAHHH Get data functionality is your solution. Get your data files from dropbox not you local machine and always sync your files online in dropbox.
Great video! How can I link multiple input/output workbooks to 1 data & calculation workbook? Or does it have to be a 1 to 1 link of input/output to data & calculation workbook?
Shouldn't be an issue. Give it a go and see how it works. If you run into issues, upload some sample files to the forum on teachexcel and ask there and it will be easier to troubleshoot.
Hi. Can you show me.how to link insert comment to other cell ?
For example, If I delete entire column 3 in source sheet, and I make columns jump up so what was column 4 is now column 3... Instead of getting updated in destination worksheet it just #REF. It losses ref instead of updating to something that is now new column 3 values. Help pls ! :D
What if I need to send these linked workbooks to my teammates? How will it not be linked to my original file?
What if i am linking cells from another workbook with employee list. And after I had to insert employes in that list. Is there away to keep the linked cell to the same person
i am using the first way to bring a one cell value from an excel file to another by typing = and select the cell and hit enter but what is happining with me is vey strange , from time to other the value is being not accurate and different from it,s source, would you please help me ?
Would this work when the workbook attaining the data is closed?
Hi, if I share a spreadsheet from workbook -1 on Google Drive. Will the new viewer who was given access to see it, also see the spreadsheet in workbook -2 (the one that is drawing its content from -1)? Thanks
Is it possible to link comments and formats applied to the original cells?
Super trick....👏👍 Can we do it in VBA? If yes can I have link. Thanks
Hi there, glad you like the video :) You can basically do everything in VBA, we have a small guide on combining data here: www.teachexcel.com/excel-tutorial/guide-to-combine-and-consolidate-data-in-excel_1878.html and a macro to combine workbooks here that you might find useful: www.teachexcel.com/free-excel-macros/m-14,combine-workbooks-macro-excel.html
Otherwise, you can ask for more specific help in our forum: www.teachexcel.com/talk/microsoft-office?src=yt.
what causes a #connect! error?
Hello, is is possible that at a specific date it will stop updating from other workbook? For example IF today() is the day 2019-09-09 linked number stop updating and it will be constant number.
I have been trying to link file data excel sheets on my computer (One drive) & (hopefully) google docs to an excel doc on a server in a separate building. I don't connect up with that server except when I return to that building every few months. Is this possible or am I chasing a rainbow?
This was great, thanks!
Great stuff..thanks
Is they way for automatic update of data from workbooks that are not open but in system ?
would the linking of cells work if the source workbook is not opened?, In other words do all workbooks would need to be opened?
pls anyone feel free to comment :)
does it work if the workbooks are not in the same location? I mean the source workbook is from someone's onedrive and the another workbook is in my onedrive? or another scenario is it is in the same onedrive but in different folder? Im working on chrome excel version not in the desktop
It doesn't work hahaha just found out right after making this comment
hi, I have an excel contact list (like 1000 contacts), I added a sheet called Index, it contains the category of each type of supplier (Food, Hygiene, Services etc...) the main list is constantly updated, so the row which holds the headline of each category is changing when I add a new contact, this causes the hyperlink from the Index to take me to the row specified previously not the new one. Is there anyway I could hold the hyperlink to follow the headline?
It should all depend how you update the list. Upload a sample file along with this question to my forum and include exactly how you update the list with your question and it will be easier to help.
www.teachexcel.com/talk/microsoft-office?src=yt_comment
I am tryring to get values from 4 huge files and dont want to open them. My problem is that i get a #REF! error when i try to update the value from these closed files. Is there a workaroud? I dont want to have to open the files to update values.
It seems like you might want to use VBA to automate the process then. That way you could click a button and have all of the values updated automatically and also do whatever else you need. I won't get a notification if you reply to this comment, so if you'd like more help, you can always ask in the Excel forum: www.teachexcel.com/talk/microsoft-office?src=yt_comment
What if I have 200 tabs and I want to import data of Cell A1 of every tab to a Master Sheet? Is there any shortcut on that?
My first reaction is to cry for having so many tabs haha but just ask in our forum and it will be easier to help you. You might want a macro for this. www.teachexcel.com/talk/microsoft-office?src=yt_comment
@@TeachExcel hahahah 200 tabs is just an exaggeration, but on an average I regularly work on getting some details of 10 to 20 tabs and transfer it to Master Sheet. I've also heard that it is not doable via Excel formulas and I need a macro to do the trick. Thanks for your recommendation! ✌🏻
Great 🌍🌍🌍
I tried this and nothing happens
👍
Why use the absolutely unnecessary phrase 'go ahead' so often?
SORRY IT NEVER WORKED , AND I DONT KNOW WHATS THE PROBLEM REALLY I AM NOT SURE ALL THE COMMENTS HERE REGARDING THE VIDEO ARE REAL AND TESTED
If you can post a message without ALL CAPS then you should go over to the forum and clearly state what you tried and what did not work and, if you can, upload a sample file that shows the issue, and it will be much easier to help you. BUT DO NOT USE ALL CAPS IN THE FORUM!!!!!
Forum: www.teachexcel.com/talk/microsoft-office?src=yt_comment
Wayy too long though
Check out my "Excel Quickies" for quick versions of the tutorials.
bore video
yes bro it was super bore for me too, maybe this high level knowledge is beyond our brains :D ;)
Great🥰
Thank you