Forum Discussion
Feeding information automatically through to new work books.
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!!
Thank you so much Wyn!
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!!
Thanks Wyn,
I have followed your instructions, but when I went to refresh the target document it wouldn't refresh as I had deleted a few columns.
When I did it the other way using the connections facility (not using Microsoft query) it was OK when I had deleted some columns.
The idea is that I have a master list and three other target documents that don't have all the information the master list has. (So as to keep special prices for different distributors not visible to others).
Maybe I have to choose between keeping the headings or editing the columns before I send it to the distributors?
Thanks Andrea