Consolidating Multiple Sheets into One

[Pienuts]

Hi, all!

I am having issues consolidating 5 worksheets into one summary sheet. My problem is that I only wish to bring over the rows which have a value in column BE. I only actually require data from columns C:F and BE for the summary.

One more possible problem is that there are "roll-up" rows scattered throughout each sheet which would have a total in column BE which I would not want to have in the summary.

Any thoughts? I've been plunking at this for a while, but I am currently stumped.

I am using Excel 2003.

Thanks for reading!

 

[Smitty]

Have you tried using AutoFilter to narrow down the fields? From there you can use Goto-->Special-->Visible cells only to copy just the filtered data.

HTH,

 

[Pienuts]

Thanks for the reply, Smitty!<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I suppose I hadn't mentioned it, but I need this summary page to be as automatic as possible, as my boss would need it quickly and often. The filter idea could work, but that would be me doing it every week for her. But thanks for the Goto-->Special-->Visible cells only trick! I feel like I could make that work for me in the future...<o></o>
<o></o>
I have found a way - it's ugly (as are many of my solutions), but it seems to work.<o></o>
<o></o>
What I did was create a page which just mirrors all of the 5 pages I wanted the data from (I assumed there would be no more than 2000 entries per sheet - I can change that later if there's a problem). I then added a column at the end which does a simple formula for eligible rows. Then on a separate page I created a pivot table which uses the eligibility field to filter the information I need. Now I just caress the pivot, add some calculations, and I'm done!<o></o>
<o></o>
If anyone can think of anything cleaner I would love to hear it! I enjoy efficiency!

 

[Smitty]

You can create a Pivot Table that uses multiple consolidation ranges. As long as the sheets are identical in structure then it's no problem. If the sheets are different, and you have Excel 2010, then you can use the PowerPivot add-in, which will let you use asynchronous tables.

Another thing to look at with Pivot Tables is the Show Report Filter Pages option. If you create a report filter with the options that need to be changed (call it the primary data field from each dependent sheet) then the Pivot Table can automatically create a new table for each filter. It's a really slick way for creating multiple reports on the fly.

Regarding the data filter trick, you can record a macro doing it. Once you get it doing what you want you can post the code back here and someone can set it up so that it's dynamic.