Master Worksheet populated by other Worksheets?

[Steve Lundwall]

Using Excel 2003, please guide me (or tell me it can't be done) concerning
something I am trying to accomplish. I have a workbook with four worksheets.
They are named Main, West, Middle and East. People enter records on West,
Middle and East. I want these same records to show up on the Main sheet so
that sorting and analysis can be done. They don't add data on the Main
sheet. So, for example I have



West

West #1 Record01 Name Address

West #2 Record02 Name Address



East

East #1 Record01 Name Address

East #2 Record02 Name Address

East #3 Record03 Name Address

East #4 Record04 Name Address



Middle

Middle #1 Record01 Name Address

Middle #2 Record02 Name Address

Middle #3 Record03 Name Address



What I would end with on the Main sheet is:



West #1 Record01 Name Address

West #2 Record02 Name Address

East #1 Record01 Name Address

East #2 Record02 Name Address

East #3 Record03 Name Address

East #4 Record04 Name Address

Middle #1 Record01 Name Address

Middle #2 Record02 Name Address

Middle #3 Record03 Name Address



Then if anyone adds anything to one of the work sheets it will automatically
show up on the Main worksheet as the next one on the list. Main is just a
compiled view of the others and should be protected from being changed -
other than to sort and filter. Can this been done and if so, can you direct
me to instructions as to how to set this up? Thanks.



Steve

 

[Gary Brown]

If you use Data| Import External Data | New Database Query and select
Excel files from the Data Source List, this will open Microsoft Query,
where you will be able to select an Excel Files from. When selected you
can then add to a query a table from the spreadsheet and retrieve data
from this spreadsheet (Criteria and sorting can be applied if
necessary). Return the data via the Return Data Icon and all the
information from your spreadsheet will be returned to a new spreadsheet
(after where you have selected to place the data), Next go to the bottom
of this data and create a second query, linking it to your next
spreadsheet and repeat the process. Finally repeat this for the 3rd
file.

For each of the queries, (i.e. select a cell which has data in) select
Data| Import External Data | Data Range Properties, for the second and
third queries uncheck the Include Field names and for all queries
select Refresh on File Open.

When you then open this file, it will pull through the data from each
of the 3 files.

If you have problems with this, send me your 3 files and I will create
it for you. If you do this I will need the filenames and locations of
the 3 individual files.

 

[Steve Lundwall]

Gary,

I sent you an email. Hope I got the address right. Let me know if you didn't
get it. I attached the spreadsheet in question. Thanks for your help.

Steve

 

[Gary Brown]

Hi Steve,

I haven't received the email, send me a Private message with your emai
address and I will send you back my email address.

Thanks

Gar

 

[Steve Lundwall]

Gary,

Obviously I didn't get your email right the first time so sending you a
private message won't work this time either. So, to move this along, here is
my email address (just put this together)

steve.lundwall

at

state.tn.us

Thanks for the help.

Steve

 

[Gary Brown]

Steve,

I have attached a copy of what I think you want, place the four files
(which are currently zipped) into a folder c:\data\, open all.xls. This
should contain all the data within the 3 files: east, middle and west.

If you then open one of the east middle and west file, add a record.
Save the file and then reopen all.xls, you should find the record added
to your file will now appear on your all.xls file


+-------------------------------------------------------------------+
|Filename: data.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4413 |
+-------------------------------------------------------------------+

 

[Steve Lundwall]

Gary,

Sounds like just what I need, however, when I click on the link I get:

Invalid Attachment specified. If you followed a valid link, please notify
the administrator

Can you send it to me using my email address from the other message I sent?
Thanks so much for your help.

Steve

 

[Gary Brown]

Steve,

I've sent a message to your email address, let me know via this forum
if you haven't got it.

Thanks

P.S. Which forum are you on? the one I am looking at is
www.excelforum.com