Pulling data from multiple Excel Sheets to a Master

I have a workbook with multiple sheets designed to track sales activity. Currently it is set-up so only one person could realistically use it to track their progress. I would like to have individual workbooks for the Sales Associates to track their own sales activity but then have a Master Sheet that would automatically pull the raw data so I can analyze the company activities as a whole.

For example, There are two sheets below that are examples of what my Sales Associates would use to track sales, there is also a master list. They have the same layout with one exception, the master has an additional column to note which Sales Associate sheet the info came from. I would like this master to pull data from each Sales Associate Sheet (only need one sheet, not the whole book) and also add which sheet the info came from in a column. If it isn't possible to add the column on which sheet the info came from I suppose I could do a work around of adding the SA's name to every row of column A on their individual books and then have the master pull that info too.

I will be keeping each Workbook in the same folder on our internal server (Microsoft Home Server) The Location would be something like TheVault>Sales>Sales Tracking>Beard, Frank Tracking Sheet.xls.

I am using Excel 2007.

Is this possible? I appreciate any insights on how to do this.

I've done this two different ways.

First, is the sales information confidential between the sale associates? In other word, is it ok for one associate to see the other associate's information?

If everyone is on the same LAN, this is great.

Method #1 - Using a master workbook with macros to gather everyone's info.
There are advantages and disadvantages to doing this. The advantages include; data is fairly private and always accessible to each associate. The disadvantages include; data is not always accessible to the master(supervisor) and there are more workbooks to manage/service.

Method #2 - Using a shared workbook.
There are advantages and disadvantages to doing this too. The advantages include; data is always accessible to everyone and there is only one workbook. The disadvantages include; data is not as private.

After some research, I found a macro that does almost what I want it to do. I'm hoping you guys can help me get the rest of the way. Here is the Macro I'm using.

Please Login or Register  to view this content.

Here are the two problems I'm trying to fix.

1. When I run the macro the table column descriptions I have in A3:AA3 get erased. I would like them to stay.
2. The source has a lot of formulas in the cells, I would like to ignore rows that may have formulas but don't display any info (example: I have a bunch of cells that may have some thing like =IF(B5="","","John"). Want the macro to ignore any cell that may have a formula like that but has returned a blank cell("")
3. Lastly, I would like the Macro to start populating the info in a set cell (A5) each time.

Can anyone help me with these 3 things?

Thanks!

Here is something I use and I hope it may help you. I need to combine multiple workbooks which contain multiple worksheets of test data. I have the test engineers deposit the files in a share folder. This code will pop up a windows explorer box and you can select as many excel files as you want and clicking open. So if you were to run this in a blank workbook it would pull all the worksheets from all the workbooks you selected into your new workbook.

Please Login or Register  to view this content.

Thanks Weibull, that kind of works. It's slightly different than what I was looking for, but I may be able to get it to work.

Originally Posted by BradPhillips

Thanks Weibull, that kind of works. It's slightly different than what I was looking for, but I may be able to get it to work.

You Looking for help on this!! You want macro automation or formula?

I'm not sure if this helps in anyway, but you might want to look at it, if not already done.

Check this link out: http://www.rondebruin.nl/tips.htm

Hope this is useful in one way or the other.

Best,
Spi

I want calculate hours bet two dates with conditions i want result as two dimensions.
I have enclosed excel sheet please find the attachment

activity start date activity end date
4/5/2013 9:01 4/5/2013 23:00
4/6/2013 10:01 4/7/2013 23:10

if activity start time less than <10:00 am
if activity start time greater than >10:00 am

activity end date time should be completed same day 11:00 pm (23:00:00)
activity end date time should be completed next day before 11:00 pm(23:00:00)

if
one result wants calculation
if first criteria is met i want result calculate time between activity start and end time)
if secone criteria is met i want result calculate time from start time till next day 23:00:00 pm

another result want in true or fales
if first criteria is met i want result "yes activity completed same day"
if secone criteria is met i want result "yes activity completed second day"