Forum Discussion
Solved
Feeding information automatically through to new work books.
Hi,
I have Excel 2007 and I want to create a master work book with lots of separate work sheets.
I want to be able to set it up so that if I change info OR ADD a line that it will feed through to three other work books which are cut down versions of the master (ie not all the info in the master will be in the others).
I know how to enter the info so if I change something it will feed through (using the = function) but would like to know if I can do that in bulk rather than one by one.
Also is it possible if I want to add a line in the master work book and have it feed through into the other work books ?
Thanks heaps
Andrea
So the first trick is to get MSQuery to recognise a Table and you have to go through these odd steps:
1. Highlight all but the last row of your Master Data set
2. In the NameBox just above column A, type MasterData and press Enter
Then you need to edit this range to include the last row
So 1. Go to Formulas > Name Manager
2. Change the reference for the name to include the last row
Save and close the Master File
In your destination file go to
Data > Other Sources > Microsoft query > Excel and connect to the Master File
Then see the image below for the things to click
Note. If you ever refresh the query when the master file is open it doesn't work. If it's open on someone else's machine it will open up a new copy and again not work.
Other than that it should work nicely
Power Query is Excel 2010+ is way easier!!
OK, I have worked out myself how to establish the link between the source workbook and the target workbook.
So now I just need to know if I can add a line in the source work book and it automatically adds it to the target workbook?
I have tried it using the established link but it didn't work.
Thanks
Given you're on Excel 2007 you could use Microsoft Query in Excel to tap into the Master File and bring the data into separate files and automatically pull new rows of data into the separate file.
This is especially true if you set up your Master file data in Tables (Ctrl+T)
There are a few steps involved but in essence you use Data > From Other Sources > From Microsoft Query > Excel
Give it a go and let us know how you get on
Hi Wyn,
I managed to work it out with the help of my son, we used the connections facility under the data tab.
It is perfect - we just have to refresh the connection in the target workbook every time we make a change in the source work book.
The only trouble is that it didn't copy accross all the headings - I am not using the table style in my source workbook - but the target workbook automatically comes through as a table. If I used the table style in my source workbook would this make all the headings come across?
Thanks for your input!
Andrea
