Go Back  DVD Talk Forum > General Discussions > Tech Talk
Reload this Page >

Excel help: Automatically pull info from many spreadsheets to one

Tech Talk Discuss PC Hardware, Software, Internet and Other Technology

Excel help: Automatically pull info from many spreadsheets to one

Old 12-15-06, 12:56 PM
  #1  
DVD Talk Hall of Fame
Thread Starter
 
Join Date: Jul 1999
Location: Texas
Posts: 7,759
Excel help: Automatically pull info from many spreadsheets to one

I don't know if this is even possible but I've been tasked with it:

I work for a small aircraft charter company. We use Excel to keep separate schedules for each of our 5 airplanes. The schedules contain lots of information, departure and arrival times, passenger names, crew names, and crew duty on and off times. My chief pilot wants a master schedule that shows each airplane, the crew for the day, and their duty on and off times, preferable for the next month. Is there any way to automate this process? We generally update schedules several times a day and we want it to automatically update the master crew schedule, as well.

Ideas?
shaun3000 is offline  
Old 12-15-06, 01:00 PM
  #2  
Moderator
 
Groucho's Avatar
 
Join Date: Mar 2000
Location: Salt Lake City, Utah
Posts: 70,684
It's possible. You can import the data from external spreadsheets into a master spreadsheet.

But it seems to me it would be a lot easier and more efficient to do this in a relational database, even MS Access.
Groucho is offline  
Old 12-15-06, 01:04 PM
  #3  
DVD Talk Hall of Fame
Thread Starter
 
Join Date: Jul 1999
Location: Texas
Posts: 7,759
So how would I go about doing that? (Master spreadsheet) The database idea is good but change is slow in our company. In the mean time, we'll have to stick with the spreadsheets.
shaun3000 is offline  
Old 12-15-06, 01:23 PM
  #4  
DVD Talk Hall of Fame
 
Blake's Avatar
 
Join Date: Feb 1999
Location: Orange
Posts: 7,736
Just have both spreadsheets open at the same time and reference one spreadsheet from the other. That way you'll see how Excel handles it. For example, in one spreadsheet I was already working on, I created a new spreadsheet and referenced it:

='C:\Documents and Settings\bk\Desktop\[test.xls]Sheet1'!$J$23

I'm not 100% sure how they update. But lets say you make changes in test.xls. You then open up current.xls - it will ask you whether or not you want to update the data from the external links. If you do, it will query those other external spreadsheets and get the current data.
Blake is offline  
Old 12-15-06, 03:28 PM
  #5  
Stealth Moderator
 
namja's Avatar
 
Join Date: Oct 1999
Location: High Definition
Posts: 24,970
Adding to what Blake said ...

THE MOST IMPORTANT THING IS to make sure that you don't change the format of the 5 separate spreadsheets. Then the master sheet can pull data from specific CELLS of the 5 spreadsheets. If you add a row/column in any of the 5 spreadsheets, then the master sheet will get messed up.

Also, it's best to NOT use the "Update" button. Rather, open the master sheet (click "Don't Update"), then open the 5 spreadsheets one at a time. The master sheet gets automatically updated as soon as one of the 5 spreadsheets is opened.
namja is offline  
Old 12-15-06, 04:57 PM
  #6  
DVD Talk Hall of Fame
 
Join Date: Oct 1999
Location: not CT
Posts: 9,618
Why can't each plane be a series of sheets within one workbook?
BigPete is offline  
Old 12-15-06, 06:02 PM
  #7  
DVD Talk Hall of Fame
Thread Starter
 
Join Date: Jul 1999
Location: Texas
Posts: 7,759
That would work, too. I assume the idea is the same but you don't reference the file path? How would a reference another sheet within the same file?
shaun3000 is offline  
Old 12-18-06, 03:50 PM
  #8  
New Member
 
Join Date: Aug 2002
Location: Chicago
Posts: 14
step #1 enter the data in the desired cell in Sheet2 (A1, for example).
step #2 in the desired cell in sheet1, enter = Sheet2!A1.

the data in Sheet1 now updates everytime you update it in Sheet2.
fradog68 is offline  
Old 12-18-06, 04:16 PM
  #9  
DVD Talk Hall of Fame
Thread Starter
 
Join Date: Jul 1999
Location: Texas
Posts: 7,759
Am I correct to assume the data always has to be in the same cell for this to work? Depending on the number of legs on a particular day, Cell A2 may be blank, contain data for one of the legs, or contain the days date, for example. Cell A2 would have to always contain the same date, correct?
shaun3000 is offline  
Old 12-18-06, 04:17 PM
  #10  
Moderator
 
Groucho's Avatar
 
Join Date: Mar 2000
Location: Salt Lake City, Utah
Posts: 70,684
That is correct. The data must be of a static type in the source cell.
Groucho is offline  

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is On
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


Thread Tools
Search this Thread

Archive Advertising Cookie Policy Privacy Statement Terms of Service

Copyright 2018 MH Sub I, LLC dba Internet Brands. All rights reserved. Use of this site indicates your consent to the Terms of Use.