You are a SAINT! Thank you, thank you, thank you! I needed a master sheet with multiple tabs to feed each tab individually onto their own sheets, but after days of research, it seemed like Excel couldn't do it. I finally stumbled on linking, but all of the videos I found weren't making sense. This one finally accomplished what I needed! For anyone who may need this info: you can also link entire columns - when you do = and then click on the source workbook, you can click and drag to select the whole column, hit enter, and it'll feed the whole thing over to a new sheet.
Excellent! Very clear explanations and easy to understand. You've answered all questions I had about external link to a master data file. Thank you so much Dawn!
Thank you so much for this video Dawn! Your information was so easy to understand and I feel much more confident with moving forward in organizing my data. This is going to help so much :D!
Is there a way to do this for 2 shared online workbooks. We have a company wide database source that i want to pull data for our team into a separate book just for our reporting.
Hi Dawn, Thank you for this wonderful video. I wonder, can this technique be used in workbooks/worksheets that are house on the Microsoft Teams platform? I am not having success searching for how to apply the methods you outline in the video here while working in Teams. Please advise. Kindest thanks, KMKL
Excel workbooks that are available from Teams can be edited if you choose the open to open the workbook using the Excel desktop app not just direct editing in Teams. The key with linking regardless of where a file is stored (Teams files are in a SharePoint site specific to the channel), is that all users have access to linked files. The paths can also be more confusing because of how the files are stored.
Thank you. I have a similar problem. I have 2 workbooks. Each workbook contains 46 sheets and I call them week numbers 1 to 46 on both workbooks. Each workbook is unique however the 46 sheets are identical in each of their respective workbooks. So the first workbook I am trying to put a link formula into cell A3 say for week 1 . The information I am putting into that cell would be the data from G3 in week 1 of the other workbook. Once I put the formula into the first workbook in cell A3 I then will drag the formula from A3 to B17 which will then get the data from the 2nd workbook from cell G3 to H17. I can do that part quite easy as I just link the two cells straight away no problem. I then want to do the same with all of the other sheets in the work books for week 2 to week 46. i can do that easy enough but it is very time consuming. I then have an additional 25 workbooks in the same format as workbook 1 that I want to repeat the function as I have done with workbook 1. All the other workbooks would be linked to workbook 2. As I say I can do it ok but it is very time consuming and was hopeful I could take the workbook1 and select all sheets so that then I only have to do the link and drag once with the other workbooks 2 to 46 all having their own week number on it. E.G cell A3 ='[workbook1.xlsx]week 1'!G3 and then repeated for each sheet so the next sheet would say ='[workbook1.xlsx]week 2'!G3 and so on. Is there a quick workaround with this at all?
What if you are wokring with complex data where the layout may change. For example more rows or columns are added affecting the cell where the total can be found. The best bet it to name the total cell and linkto a name? However, if I have a lot of cells that would take plenty time to name is there a better way to try and make links more dynamic without having to change individual names of each cell?
Thank you Dawn. Very helpful information, clearly explained. Now, getting data from a cell is one thing and getting it from a data table is another. Are there any limitations connecting data tables between two workbooks? Greetings from Colombia.
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?
Thank you for making the linking of workbooks easy to understand. Just wondering how you could copy the formuals so that a new number would be imputed when imputed on the next line down? For example, I enter daily numbers in one work book and in another work books someone else uses the numbers for something different. They are always the same numbers. I have tried to grap the corner of the cell that has formual but it copys the same formula and doesn't advance the cell numbers down. (b12-b11 then next one would be b13-b12)
Good day. Thanks for this great video. I have a question: I have about 50 budget workbooks that I need to roll into one master. Is there a way to save the workbooks into a folder and have the master add all the workbooks together? I need to add each department's information for account and month: General Fund, Donations, Jan, Feb, Mar...Total. Thanks for any help you can offer.
Thanks My Question Is If I need two different range to be linked from WB A to B On update B .system asks A WB already open do you want to Reopen and few other confusing questions And with more complax B WB receive links from multiple work book and from multiple range from same work book ! System many times Hangs So I am trying to develop One Range from One one WB But can you solve issue Multiple Range link from multiple WB link Master One file Thanks
Hello, Dawn! I am looking to link data in multiple workbooks where I would like some way to dynamically update the data from the first to the last workbook without having to manually open and save each one. Example: data in workbook A is linked to workbook B, then workbook B is linked to workbook C. When A is updated, I like to be able to open C and have the data updated from A to B to C. Suggestions?
It's possible the automatic update for workbook links has been turned off. Go to File > Options > Trust Center > External Content and look at the Security settings for Workbook Links.
Hi Dawn, I'm having trouble linking ranges of cells. I put the = in my destination workbook, then go to the source and select a range of cells. I return to the destination and hit enter. I get an average for the selected range in the top of the range in the destination workbook, not the individual values identical to the range that I selected. What am i doing wrong please?
It looks like the snag is that you return to the destination before you hit ENTER so there may be additional cells or references added to the linking formula.
Hi - i have a master spreadsheet at work, the sheet has thousands of rows and dozens of columns. 1 of the main columns has the names of a significant number of staff members (which are not in order because rows have been gradually added over time to represent that staff member - which brings me to my question) Question: i want these staff members to fill in the subsequent columns that belong to them all the way up and down the spreadsheet - BUT without seeing the overall spreadsheet that contains the other columns for the other staff members entering their data - is there a way perhaps to do this?
Hello, not OP, but I would recommend using Power Query for this. Create separate documents per employee and have those documents use Power Query to only get the information for certain employees. Have a column that employees enter the information to. Have all of those docs in ONE FOLDER. Then on your master sheet, create another power query function that links that ONE FOLDER to grab all of the excel sheets. With that connection, you can create a database of “completed” work. Whenever you need to add tasks, add it to your original sheet. Reference the “completed” sheet (that links all of the other excel sheets) to keep track of when tasks are done. I hope this makes sense, I saw your comment and it’s similar to something I’ll be implementing.
What if I have many workbook, example, people emailing me back filled survey and I want the another workbook grabs the data from survey and on workbookResponders because you want to put all the responders together Responder 1 and calculate all the responses.
Although Teams can capture links in posts, it doesn't have the same linking features as Excel. You can however, share a workbook with links that team members can view and edit.
The key with any formulas that link or refer to other workbooks is that the users have access to the source workbooks whether they are in SharePoint, OneDrive, your local network or Teams (which uses SharePoint).
The answer, like a lot of things, is "It depends." What type of password? If a password is required to open the source workbook, then you'll need to know it as you'll be prompted for the password. If the password is to protect the worksheet from changes, you can link to the source data whether or not you know the password.
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?
Depending on your application, the Excel Power Pivot tools may be a solution to combine multiple workbooks. Although I haven't covered it yet in this channel, there are tons of videos that cover these Excel options. Here's one to check out: ruclips.net/video/CjSm5sI3z8o/видео.html
![Lethal Company Music Video 🎵 "Follow The Orders" [VERSION A]](http://i.ytimg.com/vi/QeooZB4Z7RA/mqdefault.jpg)
You are a SAINT! Thank you, thank you, thank you! I needed a master sheet with multiple tabs to feed each tab individually onto their own sheets, but after days of research, it seemed like Excel couldn't do it. I finally stumbled on linking, but all of the videos I found weren't making sense. This one finally accomplished what I needed! For anyone who may need this info: you can also link entire columns - when you do = and then click on the source workbook, you can click and drag to select the whole column, hit enter, and it'll feed the whole thing over to a new sheet.
I'm thrilled that this was helpful for your project, Tiffany! And thanks for sharing the great tip about linking an entire column. A big timesaver!
Excellent! Very clear explanations and easy to understand. You've answered all questions I had about external link to a master data file. Thank you so much Dawn!
You're welcome! I'm so glad this tutorial was helpful!
SUPER CLEAR AND LOVELY EXPLAINED!!
Thank you! I'm glad it was helpful.
Thank you so much for sharing! It's very helpful.
excellent method of teaching, very thorough, well composed in delivery!
Glad it was helpful!
Thank you Dawn, this is such a great video and is really going to make my life a lot easier trying to capture clinical trainee activity metrics 🙂👋👋👋
I'm thrilled that this training is helpful so you can more easily focus on your priorities. 😁
Thank you so much for this video Dawn! Your information was so easy to understand and I feel much more confident with moving forward in organizing my data. This is going to help so much :D!
Glad it was helpful to work with your Excel data!
This is very helpful as I learn to utilize Excel more for my work. Thank you!! I subscribed!
thank you so much, you led me in the right direction.
You are so welcome
Is there a way to do this for 2 shared online workbooks. We have a company wide database source that i want to pull data for our team into a separate book just for our reporting.
good value to lenth ratio
Hi Dawn, Thank you for this wonderful video. I wonder, can this technique be used in workbooks/worksheets that are house on the Microsoft Teams platform? I am not having success searching for how to apply the methods you outline in the video here while working in Teams. Please advise. Kindest thanks, KMKL
Excel workbooks that are available from Teams can be edited if you choose the open to open the workbook using the Excel desktop app not just direct editing in Teams. The key with linking regardless of where a file is stored (Teams files are in a SharePoint site specific to the channel), is that all users have access to linked files. The paths can also be more confusing because of how the files are stored.
Thank you.
I have a similar problem. I have 2 workbooks. Each workbook contains 46 sheets and I call them week numbers 1 to 46 on both workbooks. Each workbook is unique however the 46 sheets are identical in each of their respective workbooks. So the first workbook I am trying to put a link formula into cell A3 say for week 1 . The information I am putting into that cell would be the data from G3 in week 1 of the other workbook.
Once I put the formula into the first workbook in cell A3 I then will drag the formula from A3 to B17 which will then get the data from the 2nd workbook from cell G3 to H17.
I can do that part quite easy as I just link the two cells straight away no problem. I then want to do the same with all of the other sheets in the work books for week 2 to week 46. i can do that easy enough but it is very time consuming.
I then have an additional 25 workbooks in the same format as workbook 1 that I want to repeat the function as I have done with workbook 1. All the other workbooks would be linked to workbook 2.
As I say I can do it ok but it is very time consuming and was hopeful I could take the workbook1 and select all sheets so that then I only have to do the link and drag once with the other workbooks 2 to 46 all having their own week number on it.
E.G cell A3 ='[workbook1.xlsx]week 1'!G3 and then repeated for each sheet so the next sheet would say ='[workbook1.xlsx]week 2'!G3 and so on.
Is there a quick workaround with this at all?
What if you are wokring with complex data where the layout may change. For example more rows or columns are added affecting the cell where the total can be found. The best bet it to name the total cell and linkto a name? However, if I have a lot of cells that would take plenty time to name is there a better way to try and make links more dynamic without having to change individual names of each cell?
Great Video.
Count a LIKE from me, too.
Just wondering🤨 if I can link my file with source file keeping all the formatting.....!
The links connect to the data but the formatting is in the destination worksheet not the source worksheet.
Thank you Dawn. Very helpful information, clearly explained. Now, getting data from a cell is one thing and getting it from a data table is another. Are there any limitations connecting data tables between two workbooks? Greetings from Colombia.
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?
Thank you for making the linking of workbooks easy to understand. Just wondering how you could copy the formuals so that a new number would be imputed when imputed on the next line down? For example, I enter daily numbers in one work book and in another work books someone else uses the numbers for something different. They are always the same numbers. I have tried to grap the corner of the cell that has formual but it copys the same formula and doesn't advance the cell numbers down. (b12-b11 then next one would be b13-b12)
It's possible the formula has an absolute reference such as $B$12 which means Excel won't copy down the logic, it will copy the literal formula.
@@thesoftwarepro
Thanks for the reply. Is there a way to remove the absolute value so it will be more user friendly to drag and copy down?
Good day. Thanks for this great video. I have a question: I have about 50 budget workbooks that I need to roll into one master. Is there a way to save the workbooks into a folder and have the master add all the workbooks together? I need to add each department's information for account and month: General Fund, Donations, Jan, Feb, Mar...Total. Thanks for any help you can offer.
Does this work on office 2016?
Yes, linking options in Excel are supported in earlier versions too.
Thanks
My Question Is
If I need two different range to be linked from WB A to B
On update B .system asks A WB already open do you want to Reopen and few other confusing questions
And with more complax
B WB receive links from multiple work book and from multiple range from same work book ! System many times Hangs
So I am trying to develop One Range from One one WB
But can you solve issue
Multiple Range link from multiple WB link Master One file Thanks
Hello, Dawn! I am looking to link data in multiple workbooks where I would like some way to dynamically update the data from the first to the last workbook without having to manually open and save each one. Example: data in workbook A is linked to workbook B, then workbook B is linked to workbook C. When A is updated, I like to be able to open C and have the data updated from A to B to C. Suggestions?
It's possible the automatic update for workbook links has been turned off. Go to File > Options > Trust Center > External Content and look at the Security settings for Workbook Links.
Hi Dawn, I'm having trouble linking ranges of cells. I put the = in my destination workbook, then go to the source and select a range of cells. I return to the destination and hit enter. I get an average for the selected range in the top of the range in the destination workbook, not the individual values identical to the range that I selected. What am i doing wrong please?
It looks like the snag is that you return to the destination before you hit ENTER so there may be additional cells or references added to the linking formula.
Hi - i have a master spreadsheet at work, the sheet has thousands of rows and dozens of columns. 1 of the main columns has the names of a significant number of staff members (which are not in order because rows have been gradually added over time to represent that staff member - which brings me to my question)
Question: i want these staff members to fill in the subsequent columns that belong to them all the way up and down the spreadsheet - BUT without seeing the overall spreadsheet that contains the other columns for the other staff members entering their data - is there a way perhaps to do this?
Hello, not OP, but I would recommend using Power Query for this.
Create separate documents per employee and have those documents use Power Query to only get the information for certain employees. Have a column that employees enter the information to. Have all of those docs in ONE FOLDER.
Then on your master sheet, create another power query function that links that ONE FOLDER to grab all of the excel sheets. With that connection, you can create a database of “completed” work.
Whenever you need to add tasks, add it to your original sheet. Reference the “completed” sheet (that links all of the other excel sheets) to keep track of when tasks are done.
I hope this makes sense, I saw your comment and it’s similar to something I’ll be implementing.
Thanks for the follow-up and great suggestions, @ivycarter2536. It sounds like you've created an excellent Power Query solution.
What if I have many workbook, example, people emailing me back filled survey and I want the another workbook grabs the data from survey and on workbookResponders because you want to put all the responders together Responder 1 and calculate all the responses.
Microsoft Forms might be a better solution to gather survey data into one worksheet.
How can this be done on Google Sheets?
Unfortunately, I don't have an answer as I don't use or support Google Sheets.
How can you do this with Microsoft Teams?
Although Teams can capture links in posts, it doesn't have the same linking features as Excel. You can however, share a workbook with links that team members can view and edit.
How can I maintain the formula if the workbooks are in sharepoint?
The key with any formulas that link or refer to other workbooks is that the users have access to the source workbooks whether they are in SharePoint, OneDrive, your local network or Teams (which uses SharePoint).
what if the source workbook is protected with password? can we still link ?
The answer, like a lot of things, is "It depends." What type of password? If a password is required to open the source workbook, then you'll need to know it as you'll be prompted for the password.
If the password is to protect the worksheet from changes, you can link to the source data whether or not you know the password.
its getting tasky if i need to do it for hundreds
Anybody know a simpler way with less talking and opinion? Like 5 min or so or is this just hard?
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?
Depending on your application, the Excel Power Pivot tools may be a solution to combine multiple workbooks. Although I haven't covered it yet in this channel, there are tons of videos that cover these Excel options. Here's one to check out: ruclips.net/video/CjSm5sI3z8o/видео.html