If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Consolidate data from different files
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 |
#2
|
|||
|
|||
Consolidate data from different files
Gizmo wrote...
... First off, your company should be using a database to organize this information. Using this sort of spreadsheet structure is BEGGING for 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/groups?selm=hk...wsranger.c om 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. In order to use a pivot table, you need the source data in a tabular format. In order get the data into a tabular format, you need to consolidate data from all of the relevant workbooks. This could best be 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 could be improved as long as you retain any directory and file structure. However, you should avoid changing these workbooks, or make any changes *below* or *to the right of* any existing used cells. However, you shouldn't be using spreadsheets. This sort of thing cries out for using a database. If your company is too cheap to buy one, then you must realize you work for a penny wise-pound foolish organization. Is it in your own long term interest to remain there? Then again, you could suggest to the powers that be that a database system would allow more secure storage, better maintenance of data and erferential integrity, and greater flexibility. --- Message posted from http://www.ExcelForum.com/ |
#3
|
|||
|
|||
Consolidate data from different files
Harlan I have a similar issue but not as big in scale.
My vast amounts of data are forecasts, created from models I have created in excel. I too have some giant worksheets that consolidate data into tabular form linked to multiple external workbooks. This giant tabular worksheet in turn is extracted by other models using sumproduct formulas to help in other forecasts. My question is this: Would it be a better process to take the data I forecast in Excel, and collect it in Access? I would then have to use Access to provide data for subsequent Excel models. Thanks. "hgrove " wrote: Gizmo wrote... ... First off, your company should be using a database to organize this information. Using this sort of spreadsheet structure is BEGGING for 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/groups?selm=hk...wsranger.c om 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. In order to use a pivot table, you need the source data in a tabular format. In order get the data into a tabular format, you need to consolidate data from all of the relevant workbooks. This could best be 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 could be improved as long as you retain any directory and file structure. However, you should avoid changing these workbooks, or make any changes *below* or *to the right of* any existing used cells. However, you shouldn't be using spreadsheets. This sort of thing cries out for using a database. If your company is too cheap to buy one, then you must realize you work for a penny wise-pound foolish organization. Is it in your own long term interest to remain there? Then again, you could suggest to the powers that be that a database system would allow more secure storage, better maintenance of data and erferential integrity, and greater flexibility. --- Message posted from http://www.ExcelForum.com/ |
#4
|
|||
|
|||
Consolidate data from different files
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\"&SubDi r&"\["&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 "hgrove " wrote ... .... 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/groups?selm=hk...wsranger.c om .... |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Struggling with MS Query... | Alex | General Discussion | 5 | July 6th, 2004 11:46 AM |
Countif with 2 or more data ranges in same column | Doug | Worksheet Functions | 1 | July 4th, 2004 08:57 AM |
Mial merge data base problems | Rachael | Mailmerge | 16 | May 21st, 2004 06:22 PM |
GPO Office 2003 | Tony | Setup, Installing & Configuration | 1 | May 12th, 2004 10:42 AM |
A macro to open files, refresh data, and close file in background? | Don Guillett | Worksheet Functions | 1 | May 5th, 2004 12:20 PM |