You sir are a lifesaver. I am not at all good with Excel but have recently been given tasks to do in it and didn't know where to start. You can't even begin to imagine how helpful this has been. Thank you!
This is an amazing video, thank you! Do you know a workaround to creating it as a table and when it mirrors, to do so without "0" values where there is no data?
Great Video however I am trying to create separate workbooks based on the master workbook and filtering data for each Employee with their respective cases. I wan to be able for the Individual's workbook to update the master workbook as well as When entering data in the Master it updates the Employee's Workbook. Is there a way to do this (i.e. bidirectional updating).
Yes, it is possible to create bidirectional updating between the master workbook and individual workbooks using Excel's data linking and external data sources features. Here's a general outline of how you could set this up: Create a master workbook that contains all the data for all employees. Create separate workbooks for each employee that will contain filtered data based on their respective cases. In each individual workbook, create an external data source that links to the relevant data in the master workbook using Excel's "Data" tab and "From Other Sources" function. Filter the data in the individual workbook to show only the relevant cases for that employee. Use Excel's "Workbook Links" feature to establish a link between the individual workbook and the master workbook. This will allow updates made in the master workbook to be automatically reflected in the individual workbook, and vice versa. To set up bidirectional updating between the master workbook and individual workbooks, you'll need to make sure that the data in each workbook is formatted consistently and that the external data source connections are properly configured. You may also need to use Excel's "Data Validation" and "Data Consolidation" features to ensure that data is entered correctly and that it is aggregated and summarized appropriately across all workbooks. Note that bidirectional updating can be complex and may require some advanced Excel skills, so it's important to carefully plan and test your setup before using it in a production environment.
This is a very helpful video Chester. Is there a way, via the Table Reference, to copy and update a subset of rows according to a formula/value? I see the option for column but I could not find anything for rows
Hi! I found this so useful. The only question I ask is whether there is also a way to get the cell formatting to come across? For example. In my first column I have Topics, and the second column as subtopics. Some of the cells in the Topic column have been merged as they have multiple subtopics underneath them. Is there a way that I can get the merge cell function to also duplicate onto the additional sheets?
Is there a way to add additional links rows of new information (beyond the highlighted part) at the bottom of the master sheet so it will be shown on the linked sheets? Or do you have to continuously update the master sheet everytime you have info along a new row?
Chester - I love your video and methods of linking 2 workbooks. I used the Power Query method and the data linked over correctly. However, no matter how I try, I cannot get the Refresh process to work (17:25 of the video). Any thoughts?
Thank you! Just a small question. I used Power Query method today in my excel sheet. When I added a new row to the original table and refreshed the query, an error popped up that said "This won't work because it would move cells in a table on your worksheet". I do have a couple of tables below my query table. Is there a work around for this?
Your video and tutorials here are so helpful! Is there a way to automatically combine 2 different columns (both containing words) from one sheet and populate into another much like these examples? For instance, I have an archival inventory Excel file where one worksheet has within it the phrase "box 1" (and box 2, and so on) and the adjacent column contains the descriptive text of what is in each box. I'd like to auto populate the combined info from those 2 columns into another excel file, so that the cell in the new file would say "Box 1: description...".
thumbs up as always for the very valuable lessons as always. Subscribed from the very 1st lesson. Thanks a lot Chester for your valuable time and hard work.
This is a big help. Do you know how to link cells from sheets 1-10 to another workbook on sheets from 1-10 as well. Ex workbook 1 (sheets 1-10 cell B12) link to workbook 2 (sheets 1-10 cell B12)
Thank you so much - you are a life saver! This was so helpful and straightforward! Please keep posting. Question for you: I see that if you add/modify in the original table it will reflect in the linked table. Is there a way to also make that go in reverse?
This was really helpful. My only issue is that now that I have linked spreadsheets, there doesn't appear like I can do any sorting or filtering on the linked workbook. Is there a workaround?
Chester, thank you very much for the way you explain this, one thing I have a question on is... when I am transferring a column from work book 1(master workbook, the one I enter data on) to workbook 2, like a group of names, I would like the names to stay on workbook 2 even if the name is removed off of workbook 1 and further more If I enter the name in workbook1 the second time say...in about a couple months apart I do not want it to duplicate on workbook 2, I can't seem to figure it out. Oh, I forgot to mention that my workbook 1 is made up of 6 worksheets and some of the data that is entered on the 1st 4 pages of worksheets I have being transferred to pages 5 and 6. I want to pull a couple of columns off of page 6 and be transferred to a new note book which will keep a group of names down a column so that I can total up at the end of a calendar year.
Hi Chester. Can I pick a specific data from Table and paste it into the other sheet. For example, from the above table can I only get the data where payment team is 30 days.
Excellent video! However, what I'm looking for is different. How do I auto highlight a name from a separate sheet when the specific name is chosen from a drop down list?
Hi Chester, Thanks for sharing the tricks & tips in Excel... this was a great help. i have another issue. My other team is updating data in excel, they mention city name in one column. i want to link that file with my file saved in my o365 account. but i don't want all data, I want data with the specific city to be linked in my excel. how can i do that? pls help.
Is it possible to do what you did in the video using either method 2 or 3, but only pull rows that contain a certain data point within one of the columns?
Great video .. I have a question, what if my new table is in a different format than the source ? Like let’s say the rows in the source is my columns in my new table , how can I pull the data from there? Thanks
In practicality.. we will not be able to get to convert the source file to tables all the time..but we will want our data updated in our sheet.. So the source is just excel no table but the destination is table or what ever is fine. .May be a extending this video to cover that aspect would be great sir.. Between great voice and patient explanation
Is there any way of linking sheets so that updates made in one of the sub sheets will update the main sheet? Example, someone updates a subsheet with notes, that those notes will go back to the main sheet. So updates can go both directions, not just one direction.
Great video. In the fist metod how to not have a "0" value on the update sheet, when is in the main sheet blank cell? I would like to have blank cell too in the update sheet.Thanks for help.
how do you setup a google sheet so you can add a line somewhere in the middle and it will update the info and extra line in another sheet. Would you have to do that insert tabel thing you suggested? putting in an equel sign is the easiest method for me to remember. I'm not sure I would remember the table scenairo if something went wrong. I would like to set up some sheets ( I already have a google sheet linked to a website which could be considered the main sheet that is alwasy correct) I have another sheet that I use to order stock but if I add a new item in the main sheet it does not update the second sheet. I have to go into that sheet and do it manually. And I have several sheets linked to the main sheet. How can I update just one sheet so the information replicates on to all the other sheets - say for instance a stock item which is listed in one category which is listed in a larger stock sheet of many categories? does that makes sense? Do you have a video on how to do that?
Chester, Thankyou for the tutorial. I highlight data I've updated in the source sheet using a different font colour and I need that same font colour to automatically copy through to my linked sheet. How do I do that?
This was so helpful, but how do I het the data and not the words to just paste over? I do not want the column headings, just the data? How would I achieve that?
Is there a way to update your original table from the a cell in another sheet, if a condition is met? Or do the updates just have to come from the parent table? (For example, let's just say that I don't want my staff to see the table, but I want them to be able to update it from another worksheet that is referenced with your methods. I have a patient entry form (for entering data) that is stored to a table, and I have another sheet ("Update Records") that is used to provide updates or additional notes to the parent information; I can use INDEX MATCH to bring over the data based on a search for the patient name, but how would additional notes be saved to the parent table if entered on the "Update Records" table)
Hi Chester, when I copy the entire column as reference it has the sheet name 'Sheet1'!RC all the entire column. I tried to replace RC as cell reference A1 but error message Name? how to fix this? thanks a lot
Help, I need to have specific cells on one sheet update from data that will come in from the next cell down on the origin sheet, The users on the origin sheet will enter data in the next cell down each time, the main sheet cells will not change they just need to show the updated data from the next cell down. How would I do that. Thank you for your help
Hi I have a worksheet which used various inputs to calculate a score value. These inputs are updated daily meaning the output score value updates daily. I have been looking for a formula that will allow me to automatically keep a record of these daily values to allow a trend graph to be produced over time.
I have a costs file for fiscal year 2021, which is a huge file with complex data such as salaries, supplies, specializations, and other costs in tables. At the end is the ledger (GL). I want to upload additional data to the file, such as costs for the year 2022. How can I do that, knowing that the file was created by an external company? How can I add data, knowing that my device is a Mac?
Linking only works if both workbooks are open. For example, if I have closed workbook 2 with the monthly plan, but I want to show the plan for the week on the open workbook 1. I select the desired week using the dropdown list in workbook 1. Is there any solution for the above problem? Thanks.
Mr. Chester, I've tried your method linking table between workbooks, but somehow after I selected whole table range, my excel online, didn't show square brackets[#ALL], it showed something like xxxxx$A$1:$D$4, so when I add new row, the linked table didn't show new row as well. What should I do. Thank you.
Is it possible to link data from another workbook into an existing sheet/table using Power Query? I'm trying to link the data without creating a new sheet.
Is there a way using the table way or power query to filter out certain values. For example in my main sheet i have all products. But in another tab i only want products associated with a certain branch, for example Branch1, which is written in a column under Branches. Does anyone know how to do this. so example in the main table there are 600 product items. In the other tab which only contains data related to branch1, there is only 200 items thats i want to show.
I've been doing exactly this for a read only live parts stock workbook. I tried CHOOSE & FILTER functions as well but they don't seem to work using a shared cloud based workbook.
Pls tell me how to move and auto fill data in excel from one work book into another workbook. All the videos make on sheet to sheet but I not see a single vedeos from one work book to another work book
Didn't end up being the precise workflow I was looking for, but I still watched the whole vid because of how clear and useful it was!
9:14 I just spent all day trying to find how to do this one thing.
You have saved me ❤
You sir are a lifesaver. I am not at all good with Excel but have recently been given tasks to do in it and didn't know where to start. You can't even begin to imagine how helpful this has been. Thank you!
SAME HERE
Thanks!
Thank you for showing all three! Most I have found have only shown the first and most common. Your video should be at the top of the list!
Thanks! So helpful -
You're welcome!
Excellent communication. Taught in a very easy to understand way.
Excellent Information Sir! Very helpful. Thank you so much!
I learned something new today. Excellent tutorial! Thank you 🙂
Thank you sir, I found my solution in this tutorial, well presented.
This was extremely helpful. Thank you so much!
Thank you so much for this amazing video! You explain it so well! I’m going to call you the “Excel Guru”. 😂❤
Thank you...helps a lot! 🎉🙂
This is an amazing video, thank you! Do you know a workaround to creating it as a table and when it mirrors, to do so without "0" values where there is no data?
Great Video however I am trying to create separate workbooks based on the master workbook and filtering data for each Employee with their respective cases. I wan to be able for the Individual's workbook to update the master workbook as well as When entering data in the Master it updates the Employee's Workbook. Is there a way to do this (i.e. bidirectional updating).
Hi did you end up finding the answer to your question?
Yes, it is possible to create bidirectional updating between the master workbook and individual workbooks using Excel's data linking and external data sources features.
Here's a general outline of how you could set this up:
Create a master workbook that contains all the data for all employees.
Create separate workbooks for each employee that will contain filtered data based on their respective cases.
In each individual workbook, create an external data source that links to the relevant data in the master workbook using Excel's "Data" tab and "From Other Sources" function.
Filter the data in the individual workbook to show only the relevant cases for that employee.
Use Excel's "Workbook Links" feature to establish a link between the individual workbook and the master workbook. This will allow updates made in the master workbook to be automatically reflected in the individual workbook, and vice versa.
To set up bidirectional updating between the master workbook and individual workbooks, you'll need to make sure that the data in each workbook is formatted consistently and that the external data source connections are properly configured. You may also need to use Excel's "Data Validation" and "Data Consolidation" features to ensure that data is entered correctly and that it is aggregated and summarized appropriately across all workbooks.
Note that bidirectional updating can be complex and may require some advanced Excel skills, so it's important to carefully plan and test your setup before using it in a production environment.
thank you indeed for your session it was very useful.
Explained in a easy manner, thank you so much.
Thank you! Im getting a good view on what to do now. May I know if you have a sample of a power query with multiple columns like asset management?
This is a very helpful video Chester. Is there a way, via the Table Reference, to copy and update a subset of rows according to a formula/value? I see the option for column but I could not find anything for rows
Not a single "erm". How do people do that. Good video. Thanks.
This helped me a lot in my work, thank you very much 🙂
Thank you, the video is very helpful for me.
This was a suberb video. Loved it. Thanks
At 7:40 what if we need to transfer and update 100k+ rows ? please help
Thank you for the video, I can use the different methods as needed. the information was given with very clear steps for each method.
Hi! I found this so useful. The only question I ask is whether there is also a way to get the cell formatting to come across? For example. In my first column I have Topics, and the second column as subtopics. Some of the cells in the Topic column have been merged as they have multiple subtopics underneath them. Is there a way that I can get the merge cell function to also duplicate onto the additional sheets?
Love you Chester, great work, keep it up.
Thanks, will do!
Great video, very heplful, it's sad they do not teach us these very useful tools in Universities. Thanks a lot!
Great explaination, thank you. Can you use this method to create new columns with specific formulas?
Is there a way to add additional links rows of new information (beyond the highlighted part) at the bottom of the master sheet so it will be shown on the linked sheets? Or do you have to continuously update the master sheet everytime you have info along a new row?
Chester - I love your video and methods of linking 2 workbooks. I used the Power Query method and the data linked over correctly. However, no matter how I try, I cannot get the Refresh process to work (17:25 of the video). Any thoughts?
Thanks for this video! What if I have a workbook that I want the whole sheet except for one column to transfer, who do I hide the one column?
Thank you! Just a small question. I used Power Query method today in my excel sheet. When I added a new row to the original table and refreshed the query, an error popped up that said "This won't work because it would move cells in a table on your worksheet". I do have a couple of tables below my query table. Is there a work around for this?
Your video and tutorials here are so helpful! Is there a way to automatically combine 2 different columns (both containing words) from one sheet and populate into another much like these examples? For instance, I have an archival inventory Excel file where one worksheet has within it the phrase "box 1" (and box 2, and so on) and the adjacent column contains the descriptive text of what is in each box. I'd like to auto populate the combined info from those 2 columns into another excel file, so that the cell in the new file would say "Box 1: description...".
thumbs up as always for the very valuable lessons as always. Subscribed from the very 1st lesson. Thanks a lot Chester for your valuable time and hard work.
My pleasure!
Thanks for great video. How did you get rid of those zeros while sharing two workbooks?
Hi - I loved the video. Is there any chance that the edits can be bi-directional?
This is a big help.
Do you know how to link cells from sheets 1-10 to another workbook on sheets from 1-10 as well.
Ex workbook 1 (sheets 1-10 cell B12) link to workbook 2 (sheets 1-10 cell B12)
Thank you. Your tipps saved me a lot of time :)
Great to hear!
Brilliant and very well explained
Thank You!
Great and useful information: thank you.
Thank you so much - you are a life saver! This was so helpful and straightforward! Please keep posting. Question for you: I see that if you add/modify in the original table it will reflect in the linked table. Is there a way to also make that go in reverse?
Hello Brandon, I have the same question. Did you find an answer?
@@Fabi_terra i think yes but instead link from original to outside, do it from outside to inside
This was really helpful. My only issue is that now that I have linked spreadsheets, there doesn't appear like I can do any sorting or filtering on the linked workbook. Is there a workaround?
Thank you Chester. A great explantion.
Chester, thank you very much for the way you explain this, one thing I have a question on is... when I am transferring a column from work book 1(master workbook, the one I enter data on) to workbook 2, like a group of names, I would like the names to stay on workbook 2 even if the name is removed off of workbook 1 and further more If I enter the name in workbook1 the second time say...in about a couple months apart I do not want it to duplicate on workbook 2, I can't seem to figure it out. Oh, I forgot to mention that my workbook 1 is made up of 6 worksheets and some of the data that is entered on the 1st 4 pages of worksheets I have being transferred to pages 5 and 6. I want to pull a couple of columns off of page 6 and be transferred to a new note book which will keep a group of names down a column so that I can total up at the end of a calendar year.
THANK YOU SOO MUCH SIR!
Hi Chester. Can I pick a specific data from Table and paste it into the other sheet. For example, from the above table can I only get the data where payment team is 30 days.
Wow I saw how many videos n it was this ez!!!!
hello, is it possible to keep formatting from one worksheet to another.
Excellent video! However, what I'm looking for is different. How do I auto highlight a name from a separate sheet when the specific name is chosen from a drop down list?
Hi Chester,
Thanks for sharing the tricks & tips in Excel... this was a great help.
i have another issue. My other team is updating data in excel, they mention city name in one column. i want to link that file with my file saved in my o365 account. but i don't want all data, I want data with the specific city to be linked in my excel.
how can i do that? pls help.
Brilliant as always. Thanks so much for your time...
Does adding a row in the middle of the table still update across linked worksheets/workbooks? That's the issue we are having right now. Thank you! :)
Never mind, it does for the table method. Thank you for the clear/helpful instructions!
Chester, thanks. Simple explanation, easy to do!!!
You're welcome!
Excellent explanation, thank you!
Is it possible to do what you did in the video using either method 2 or 3, but only pull rows that contain a certain data point within one of the columns?
Great video .. I have a question, what if my new table is in a different format than the source ? Like let’s say the rows in the source is my columns in my new table , how can I pull the data from there? Thanks
Brilliant video! thanks a lot
Solve my problem, thanks💕
In practicality.. we will not be able to get to convert the source file to tables all the time..but we will want our data updated in our sheet.. So the source is just excel no table but the destination is table or what ever is fine. .May be a extending this video to cover that aspect would be great sir.. Between great voice and patient explanation
Thanks!!!
Very Helpful! Thank you so much!
Hi! Is it possible to sync the 2 workbooks? Mean can you update the second workbook and the original workbook will be updated automatically?
how did you automatically formated the data in 9:20 ?
thanks, my man
Is there any way of linking sheets so that updates made in one of the sub sheets will update the main sheet? Example, someone updates a subsheet with notes, that those notes will go back to the main sheet. So updates can go both directions, not just one direction.
Following
Great video. In the fist metod how to not have a "0" value on the update sheet, when is in the main sheet blank cell? I would like to have blank cell too in the update sheet.Thanks for help.
how do you setup a google sheet so you can add a line somewhere in the middle and it will update the info and extra line in another sheet. Would you have to do that insert tabel thing you suggested? putting in an equel sign is the easiest method for me to remember. I'm not sure I would remember the table scenairo if something went wrong.
I would like to set up some sheets ( I already have a google sheet linked to a website which could be considered the main sheet that is alwasy correct) I have another sheet that I use to order stock but if I add a new item in the main sheet it does not update the second sheet. I have to go into that sheet and do it manually. And I have several sheets linked to the main sheet. How can I update just one sheet so the information replicates on to all the other sheets - say for instance a stock item which is listed in one category which is listed in a larger stock sheet of many categories? does that makes sense? Do you have a video on how to do that?
If I would like to link one file with other computers? What can I do?
Is there a way to mirror data across worksheets and keep all conditioning as well? With formulas
sir yr video very useful. I have one doubt how to combine salary data one sheet and salary advance one sheet in excel
Chester, Thankyou for the tutorial. I highlight data I've updated in the source sheet using a different font colour and I need that same font colour to automatically copy through to my linked sheet. How do I do that?
You might want to look at conditional formatting with the rule of when the cell is not empty.
Thank you!
Hi Chester, I'm wondering if there is a formula to link merge cells into a single cell.
What about look data on Merge Cells?
This was so helpful, but how do I het the data and not the words to just paste over? I do not want the column headings, just the data? How would I achieve that?
get not het
sorry my spelling is horrendous!
Is there a way to update your original table from the a cell in another sheet, if a condition is met? Or do the updates just have to come from the parent table? (For example, let's just say that I don't want my staff to see the table, but I want them to be able to update it from another worksheet that is referenced with your methods. I have a patient entry form (for entering data) that is stored to a table, and I have another sheet ("Update Records") that is used to provide updates or additional notes to the parent information; I can use INDEX MATCH to bring over the data based on a search for the patient name, but how would additional notes be saved to the parent table if entered on the "Update Records" table)
Hi Chester, when I copy the entire column as reference it has the sheet name 'Sheet1'!RC all the entire column. I tried to replace RC as cell reference A1 but error message Name? how to fix this? thanks a lot
Thank You!!!!
Hello What if I want to use the data to make contribution statements. Is there a way I can use the table and not have to copy and paste each cell?
THANK YOU!
Can I use a spreadsheet of dates and events to link to a excel calendar that I made all within the same workbook?
How can new rows be added in the data of original sheet and have it be immediately reflected on the linked sheet? Thank you.
so helpful
Help, I need to have specific cells on one sheet update from data that will come in from the next cell down on the origin sheet, The users on the origin sheet will enter data in the next cell down each time, the main sheet cells will not change they just need to show the updated data from the next cell down. How would I do that. Thank you for your help
Hi
I have a worksheet which used various inputs to calculate a score value.
These inputs are updated daily meaning the output score value updates daily.
I have been looking for a formula that will allow me to automatically keep a record of these daily values to allow a trend graph to be produced over time.
I have a costs file for fiscal year 2021, which is a huge file with complex data such as salaries, supplies, specializations, and other costs in tables. At the end is the ledger (GL). I want to upload additional data to the file, such as costs for the year 2022. How can I do that, knowing that the file was created by an external company? How can I add data, knowing that my device is a Mac?
Linking only works if both workbooks are open. For example, if I have closed workbook 2 with the monthly plan, but I want to show the plan for the week on the open workbook 1. I select the desired week using the dropdown list in workbook 1. Is there any solution for the above problem? Thanks.
Mr. Chester, I've tried your method linking table between workbooks, but somehow after I selected whole table range, my excel online, didn't show square brackets[#ALL], it showed something like xxxxx$A$1:$D$4, so when I add new row, the linked table didn't show new row as well. What should I do. Thank you.
Is it possible to link data from another workbook into an existing sheet/table using Power Query? I'm trying to link the data without creating a new sheet.
Hi and thank you for a very useful video!! Do these work on worksheets that contain the same data but are sorted differently??
Will the second method work on Pivot Tables? Thank you
How about linking 2 sheets in one new sheet? Pls help!
Can you link workbooks, but only update data within a specified date range?
can you pls tell me how to update fill colour too with text in another sheets
Can set formula in column range?
Is there a way using the table way or power query to filter out certain values. For example in my main sheet i have all products. But in another tab i only want products associated with a certain branch, for example Branch1, which is written in a column under Branches. Does anyone know how to do this. so example in the main table there are 600 product items. In the other tab which only contains data related to branch1, there is only 200 items thats i want to show.
Even i want the same things brother 😂
I've been doing exactly this for a read only live parts stock workbook. I tried CHOOSE & FILTER functions as well but they don't seem to work using a shared cloud based workbook.
Pls tell me how to move and auto fill data in excel from one work book into another workbook. All the videos make on sheet to sheet but I not see a single vedeos from one work book to another work book