Feeding information automatically through to new work books.

[Andy Connockity]

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

[#2]

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

[#3]

Hi @Andy Connockity

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

[#4]

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

[#5]

I justed tested the scenario with the source workbook in table format and used the connections facility. 

Same result - not all the headings came across. 

In the source document, when I converted it to a table, all the columns that were very narrow expanded to fit the heading description.

When I made the connection and imported the data, SOME of the columns that were narrow stayed narrow and had no heading whereas some others that were narrow became extra wide (wider than the description) and retained the headings. 

All very puzzling, but I would like to fix this as I don't want to edit the headings every time I refresh the data. 

Thanks again for your input. 

Andrea

[#6]

Hi Andrea

Using a Table will llow you to pull the headings through, however, I forgot that there are a couple of extra things you need to do.

I'll pull together some screenshots tonight and post them here

[#7]

Thank you so much Wyn!

[#8 · Accepted solution]

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!!

[#9]

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