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