data consolidation best route????

[kristal9]

Looking for words of wisdom here as to the best route to take.

I have several Excel files with multiple tabs for each file. (Each file for instance is a sales division and each tab represents a sales team). On the tabs, the users - sales people - fill out data in rows. (I'm using data validation with drop downs because that's the only way to get good data back from sales input) The other thing is the source files header row is row two and not row one.

My next step is to consolidate all of this information together into one large file. In all there are over 8 spread sheets and 30 different tabs.

The data needs consolidating every week.

What's the best option?

I thought of Access with linking tables but can only get a linked table for each tab and not one consolidated table. The other thing is that the source Excel files will change names each week. (e.g BobsGroup_we05.30.xlsx) The other thing is the Excel files are 2007 and Access is 2003. It's very frustrating to say the least to try to get Access to read the Excel.

Can it be done with just another Excel file? That would be my preference ratehr than pound into Access only to Export to Excel for the end user. How can I accomodate for varied number of rows used per tab each week?

Thoughts?

 

[SkipVought]

Hi,

This can be done in Excel. Unless you intend to hand-hack the data each week, you'll need to write some macros to assist you in importing and appending the data.

Just as a strategy, I'd store all the new workbooks each week in one folder: move the old stuff out or delete and the new stuff in. Then, all you do is process every workbook in the folder.

I would use ADO to read and append to my master table.

Piece of cake!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}