How can I pull data from multiple user workbooks into one?

[Stuart Peters]

I have set up timesheets for my department. I have a template that I have
everyone use (About 10 people), This saves the worksheet as "CSTS Employee
number Date serial" For Example if Employee 432 created a workbook for this
week, the file saved would be "CSTS 432 38808.xls". They then mail me this
template every week.
I enter the data from their totals category into a master workbook which has
totals for everyone.
1.) Is there an easy way to pull all of these into this workbook for the
year?
I was going to use the date seial to pull the data into the appropiate
location and tab of the master file.
2.) Can I pull the data from the individule files without having to pull up
the worksheets?
3.) Is there a more eloquent way to do what I want to do?

Everyone uses Office 2003

[FIRSTROUNDKO via OfficeKB.com]

Stuart,

have you thought about using vlookup





Stuart Peters wrote:
>I have set up timesheets for my department. I have a template that I have
>everyone use (About 10 people), This saves the worksheet as "CSTS Employee
>number Date serial" For Example if Employee 432 created a workbook for this
>week, the file saved would be "CSTS 432 38808.xls". They then mail me this
>template every week.
>I enter the data from their totals category into a master workbook which has
>totals for everyone.
>1.) Is there an easy way to pull all of these into this workbook for the
>year?
>I was going to use the date seial to pull the data into the appropiate
>location and tab of the master file.
>2.) Can I pull the data from the individule files without having to pull up
>the worksheets?
>3.) Is there a more eloquent way to do what I want to do?
>
>Everyone uses Office 2003

--
Message posted via http://www.officekb.com

[Stuart Peters]

Yes, I have thought about using vlookup, but If I have 10 people sending me
timesheets every week, I would have to keep 520 Workbooks in my computer just
to pull data from.
Once I get the data in the master file, I don't care what happens to the
individual worksheets.


"FIRSTROUNDKO via OfficeKB.com" wrote:

> Stuart,
>
> have you thought about using vlookup
>
>
>
>
>
> Stuart Peters wrote:
> >I have set up timesheets for my department. I have a template that I have
> >everyone use (About 10 people), This saves the worksheet as "CSTS Employee
> >number Date serial" For Example if Employee 432 created a workbook for this
> >week, the file saved would be "CSTS 432 38808.xls". They then mail me this
> >template every week.
> >I enter the data from their totals category into a master workbook which has
> >totals for everyone.
> >1.) Is there an easy way to pull all of these into this workbook for the
> >year?
> >I was going to use the date seial to pull the data into the appropiate
> >location and tab of the master file.
> >2.) Can I pull the data from the individule files without having to pull up
> >the worksheets?
> >3.) Is there a more eloquent way to do what I want to do?
> >
> >Everyone uses Office 2003
>
> --
> Message posted via http://www.officekb.com
>

[John James]

Given the size, rather than a memory-intensive sucking of data into your master worksheet (controlled from the target), a better approach may be uploading the data into a database (controlled from the source) using a macro. If all source workbooks are consistently structured, a macro in your Personal Macro Workbook attached to say a shortcut key might be used to upload.

I'd structure the upload data in the souce files into a contiguous range of rows with all necessary identifiers in those rows and then transfer that data into a single datasheet (assuming Excel was chosen as the "database") in the target.

[John James]

For code to provide a flexible formula to extract data from a specified cell in a closed file, refer:

http://www.excelforum.com/showthread.php?t=531126

http://www.*****-blog.com/archives/2...ing-workbooks/