Reporting off 7 identical workbooks

[drmmer07]

Hello All,

I have an excel issue atm with my businesses sale schedule.

What happens is there are 7 workbooks used by 7 different teams for logging when sales will be up, used as a kind of calender as such.

The workbooks are all formatted identically and are used by multiple people, however only one person can have write-access at any time to limit double ups/ errors with duplicated rows.

I need to report off the 7 workbooks such as (number of units (column z)scheduled by rep (column e) on a particular day of the current month,

I have created a massive report using HEAPS of SUMIF statements, however it takes far too long to calculate and is just too heavy for reporting purposes.

I understand my alternatives would be access? Or is there a macro i can use to populate all 7 workbooks into one then organise reporting off that?

Or...in my excel dreams, one huge pivot table containing data of the 7 workbooks.

Any help would be appreciated.

Cheers

 

[Excelestial]

I work with something similar to your dilemma. I compile my numbers into a sheet I call THE GRID...which is a table of calculated numbers by category. I use SUMIF and it doesn't take very long for me...I calculate across 7 sheets from 2004 - 2010 an average of 20K records in each, which I don't consider large. I wonder if SUMPRODUCT would be quicker for you.

 

[AnAnalyst]

Re: Reporting from 7 different but identical workbooks

Hi there,

You don't mention which version of Excel you're using.

Have you looked at using the "consolidate" option (under the data menu) at all?

Whilst I've never used this option personally, a quick search of the Excel 2007 help shows the following topic that may be of interest:

"Consolidate multiple worksheets into one PivotTable report"

HTH

 

[Joe4]

Moderator Note:

You had posted the same question in multiple forums. I combined the replies all into one thread.

If you have posted something to the wrong forum, please delete the original (you have about 5-10 minutes to do this if no one has responded), or simply click on the Report button and ask a Moderator to move it for you. Please do not post the question multiple times though. Per forum rules, threads of a duplicate nature will be locked or deleted.

Thanks.