Consolidate data from different files

[Gizmo]

Hi guys,



I have just started to work on my company's statistics and it seems to me,
that the way we report the numbers could be much more simplified and more
consistent. The present structure of the reporting files/folders is as
follows:



The company is recording (in various excel files) some data about different
countries it works with. The structure of the main folder is as follows:

In Year folder (2004) you have 12 Months folders (January to December), and
in every month folder you have some individual country files (every of them
with several sheets and like 30 different criteria you keep track of (all
criteria are basically similar between the countries, with, however, some
minor differences (additional lines for some of them)). The names are
standardized, for example the individual file is called like
"0604-Germany.xls" etc., the folder for each month is named like "06 June"
etc. However, this logical structure is not used to retrieve the data in a
fully automated way.



The actual challenge I'm confronted with are the "summary" files that keep
track of the activity per country, per period etc... Their structure seems
to me to be very rigid - there is like "x" hundred cells, every of them
including the direct reference to a given cell in particular
workbooks/worksheets, f. ex. "=K:\...\(year specified)\(month
specified)\(xls. file specified for a given country, and then the worksheet
and the exact cell, like "C4").



So, not only the creation of such "Summary" files seems to me to be very
time-consuming (with every formula entered manually - impossible to extend
them by dragging the corner of the cell because of the path to the file),
but in addition, any change in the original files (new criteria added, new
row or column, new country) influences the "summary" file, and with large
data it's difficult to keep track of all of this.



Finally, once you have the table with criteria in rows and countries in
columns (by period), you are obliged to recreate the entire table if one day
you would like to have, for example, the data organized with given criteria
in rows and periods in columns (by country).



So, I would like to ask you some questions that I'm unable to answer by
myself:



Is it possible to create the "summary file", where you do not have to put
all the references (like =K:\...) manually?

Are there any other methods of getting such type of statistics more easily
and less rigidly (for exemple with Pivot Tables?).

What are good practices in "file architecture" that can make the whole
structure more easily adaptable (relative cell referencing? VLOOKUP?). Or
maybe the above-presented method is the best I can hope for?



Thank you for getting to this point and for any comments/help/suggestions
you may have!

Hope that my explanations make sense...



Cheers,

Gizmo

 

[hgrove]

Gizmo wrote...
...

First off, your company should be using a database to organize thi
information. Using this sort of spreadsheet structure is BEGGING fo
problems.

So, I would like to ask you some questions that I'm unable to
answer by myself:

Is it possible to create the "summary file", where you do not
have to put all the references (like =K:\...) manually?
See

http://www.google.com/[email protected]

Are there any other methods of getting such type of statistics
more easily and less rigidly (for exemple with Pivot Tables?).

Yes, but you still have to pull the data from the other workbooks. I
order to use a pivot table, you need the source data in a tabula
format. In order get the data into a tabular format, you need t
consolidate data from all of the relevant workbooks. This could best b
done using alternative #1 from the previous linked article.

What are good practices in "file architecture" that can make the
whole structure more easily adaptable (relative cell referencing?
VLOOKUP?). Or maybe the above-presented method is the best
I can hope for?

There's nothing wrong with the directory and file structure that coul
be improved as long as you retain any directory and file structure
However, you should avoid changing these workbooks, or make any change
*below* or *to the right of* any existing used cells. However, yo
shouldn't be using spreadsheets. This sort of thing cries out for usin
a database. If your company is too cheap to buy one, then you mus
realize you work for a penny wise-pound foolish organization. Is it i
your own long term interest to remain there? Then again, you coul
suggest to the powers that be that a database system would allow mor
secure storage, better maintenance of data and erferential integrity
and greater flexibility

 

[Gizmo]

Thank you Harlan for your very instructive comments, and sorry for getting
back to you only right now. It took me some time to go through the ideas you
suggested in your post.

Even if I will try to put gradually in place your "database" solution (will
have to catch up some classes in MS Access !), I would like, for the time
being, to ask you (and any other person willing to help me on this) one
additional question (if you don't mind). It's about the solution nr 1 ("Use
formulas to create literal external reference formulas as text") you
proposed in the link.

I'm not sure if I understood you right. Pls correct the following text in
the places where I'm wrong.

You suggest to write down the "future" formulas in the "text" format, and
then change it (with REPLACE tool) to formulas. In your example (with
SUMPRODUCT function) I should work on something like this:

="=SUMPRODUCT(('C:\somedir\"&SubDir&"\["&Filename&"]"&WorksheetName&"'!"&Fir
stRangeAddress&"="&whatever&")*'C:\somedir\"&SubDir&"\["&Filename&"]"&Worksh
eetName&"'!"&SecondRangeAddress&")"

The goal of this would be to extend it easily through rows and columns,
changing automatically the part of the reference we are interested in,
wouldn't it be?

So, for my file (let it be
='K:\2004\01_January\[Germany_Data_01.xls]Summary'!$D$26), if I put this
link in cell "A1" (and in B1 the formula for February, to tell MS Excel how
it should follow), and than would like to extend it automatically to get in
the following columns the data for next months (March in "C1", April in
"D1") for the same cell but different workbook, what should I exactly do?
Put 01_January\[Germany_Data_01.xls] in "&01_January&"
\["&Germany_Data_01.xls&"] format? Tried and it doesn't work (gives me the
usual "formula error"). What am I doing wrong?

Thanks again for your patience with the "excel greenhorns" like me!

My respects,

Gizmo


...
....

See

http://www.google.com/[email protected]

....