Create a report from different files

T

Tom Velnosky

I need to create a report from several files with different names but from
the same cells in each file and paste them into a new file with the
information in specific locations and then calculate for totals etc...

How do I direct Excel to get the information? There may be a changing umber
of files but they will always be in the same folder.

Tom
 
C

Christine

Tom,
It should be fairly easy-- Let me work on it a little- but in Excel 2002,
there is import data and refresh imported data-- so you should be able to set
up a master file that compiles all the different ranges from all the
different work books and then "refresh" the master without too much trouble
to make master reports....
 
T

Tom Velnosky

Thanks Christine for your hard work...

Remember, all the files are in one folder but have different names. After I
have imported the information, then the refresh can I then sort and format
the data on the sheet before publishing the report?

Thanks

Tom
 
C

Christine

OK-- Here's go:

Import External Data/Make master file

1) first on all your data source files, you need to NAME RANGES. You can
follow instructions on in Help: select the cells you want to export, goto
Insert, Name, Define and type in a name for your range. (there's different
ways to name a cell)

2) Open a New workbook or open the file that you will create a "master
report" in.

3) Goto Data, Import External Data, Import Data

(ok- there are many types and complicated things that can be imported and
we're going to ignore most all of it because this is the most basic import
that exists)

4) from the Select Data Source box, change the "files of type" to EXCEL
(xls). Browse and find your folder and files, select one of your files. The
Ranges box for that file will pop up in a box (some may be print ranges).
Select the range that you created for importing.

5) After selecting the range, the Import data box will appear and ask if
you want to import that range on the existing worksheet or on a new
worksheet. You will need to decide what you want: you may want to set up a
master worksheet where each side-by-side column represents different imported
range or set everything up on separate worksheets in the file but then this
is harder to create a "master view" from-- If you pick, existing worksheet,
you will need to indicate (click on the cell) where you want the data to be
dropped.

6) You will need to repeat this for each import.

7) After you import data, you will see a special toolbar that allows for
refreshing data and setting properties. You can set your data to refresh
automatically or you can control it manually-- (you click and it goes and
gets new data).

8) I don't know who much you will be able to manipulate the data (move it
around/sort) after you get it all imported-- it shouldn't be a problem at
all- you shouldn't have any restrictions. However, to be on the safe side,
DUPLICATE (Create a copy/right click on the TAB) your master worksheet before
experimenting.

Good Luck!
 
Top