+ Reply to Thread
Results 1 to 11 of 11

Consolidate multiple sheets

  1. #1
    Registered User
    Join Date
    10-20-2009
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    58

    Consolidate multiple sheets

    There are many threads and answers to my question but none relate to choosing random sheets - unless someone can put me in the right direction??

    Out of the 6 workeets that I have, I need to be able to decided which ones I wish to consolidate onto the Summary sheet.

    The number of rows in each sheet will be different day by day so the consolidation needs to cater for this.

    The consolidation sheet needs to show all Europe rows then US rows then ME rows.

    Can anyone help or point me to the right thread?

    I have attached an example. If someone wishes to drop the example code in so I can look, learn and apply this to actual workbook, then I would be ever so grateful.

    Many thanks.
    Last edited by scottb; 06-16-2010 at 05:31 AM.

  2. #2
    Registered User
    Join Date
    10-20-2009
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    58

    Re: Consolodating multiple sheets into a summary sheet

    I also need to know how to choose which columns I wish to show as well as which sheet(s) to show.

    Also, if I have 100 opportunities and wish to only see the ones for this month, is it possible to only show the month I enter into a parameter cell and then sum the value of opportunities by that month?

    I hope that makes sense, I have added a new example to show what I mean.

    I think I may be asking too much here but there are some clever people out there...
    Last edited by scottb; 06-14-2010 at 08:31 AM.

  3. #3
    Registered User
    Join Date
    10-20-2009
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    58

    Re: Consolodating multiple sheets into a summary sheet

    Okay - I have managed to find and add the code to copy the contents from the first worksheet, which works in a fashion...

    Code is:

    Please Login or Register  to view this content.
    Can someone change the code to suit the followign issues:

    How do I choose which sheets to consolidate
    How do I choose which columns to consolidate
    How do I make the consolidated data start at B3 in GlobalSummary
    How do I stop the consolidated infomormation from overwriting my total value (row 22 in GlobalSummary)

    The Rnage needs to be expandable based upon additions or removals.

    Any help really appreciated as I struggling like mad. Thanks

    Example Attached with code
    Attached Files Attached Files
    Last edited by scottb; 06-14-2010 at 08:30 AM.

  4. #4
    Registered User
    Join Date
    10-20-2009
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    58

    Re: Consolidate multiple sheets

    Okay - Removed previous code and added the one from the link below as this does exactly what I need regarding chooing the worksheets and columns but I do not know where to enter this criteria into the code. Its beyond me.

    I have added the modules and code.

    Link to thread:http://www.excelforum.com/excel-gene...-into-1-a.html

    I have also attached the new version of my workbook.

    I am trying and not just expecting the answer but I really need some guidance.

    Thanks,
    Attached Files Attached Files

  5. #5
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Consolidate multiple sheets

    You will need to:

    1) Decide some criteria for choosing which sheets to evaluate or not. Perhaps any sheet that DOESN'T start with "RAN" and isn't the summary sheet.
    Please Login or Register  to view this content.

    2) You'll need to adjust your lastrow/nextrow evaluations to the correct column:

    3) You don't need the find column stuff since all your sheets seem to be the same layout. (remove ColARR and Clm and ColFIND dims and code referring to it)

    4) You will need to actually apply an autofilter to each sheet based on the criteria you've entered. I use a lot of autofilters in my macros.

    You can get sample code usually by just turning on the macro recorder and letting it record you doing your autofilter, then edit the code down into your macro in the transfer section.

    5) To use autofilters, you will have to delete those blank yellow rows on row2 of all your sheets. Blank rows indicate the end of the data set so the autofilter would never see the data below the blank row.


    Here's the tweaked code, you'll have to fix your sheets as noted so that it will work properly. There also wasn't enough sample data in there for me to even wonder if it's going to work as expected, as there was no sample "results" either. Give it a go...

    Please Login or Register  to view this content.
    Last edited by JBeaucaire; 06-14-2010 at 06:18 PM.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  6. #6
    Registered User
    Join Date
    06-11-2010
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    43

    Re: Consolidate multiple sheets

    Do you need to use code? I have a formula that I love using. If you move Random1 to the farthest left and Random3 to the tab jus to the right of the Global total you can type this formula in E3 =SUM(Random1:Random3!H3) . This will count everything in cell H3 from and including Random1 - Random3. If you move a tab outside the Randoms it will remove it from the total. This way you can have future periods included for year end estimates or add new Regions on the fly without complicated formula changes.

    The only issue is keeping the layout the same. So what I like to do is have some subtotals at the top of each tab that the sum is counting so if more data gets added the i don't have a worry about messing with the formula.

  7. #7
    Registered User
    Join Date
    10-20-2009
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    58

    Re: Consolidate multiple sheets

    In all the posts above, my outcome has been lost.

    I have attached a new example with data of exactly what I need the outcome to show.

    The worksheets I need are labelled (Need) and I have attached an GlobalExample sheet with the required outcome.

    On the GlobalExample sheet, the value of cell L6 will autofilter column F to that value and cell L8 will auto filterfilter column D to that value.

    The Update Report button will update the report everytime its click.

    The total value on GlobalExample will show the total value in E14 based upon the autofilter.

    I need to achieve the above for work tomorrow and am struggling with time.

    Thanks to everyone helping.
    Attached Files Attached Files

  8. #8
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Consolidate multiple sheets

    Scott, I'm trying not to get perturbed.
    Quote Originally Posted by JBeaucaire
    5) To use autofilters, you will have to delete those blank yellow rows on row2 of all your sheets. Blank rows indicate the end of the data set so the autofilter would never see the data below the blank row.
    Those yellow rows in row 2 are still there, ready to break any attempt to autofilter your data.

    Also, your need isn't lost, you just moved things around and didn't get into the code making cell reference adjustments to match, you gotta dig into it if you want to fully get it.

    I use F8 to step my way through code one line at a time so I can see what it's doing line by line... give it a shot.
    Attached Files Attached Files
    Last edited by JBeaucaire; 06-16-2010 at 10:09 AM.

  9. #9
    Registered User
    Join Date
    10-20-2009
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    58

    Re: Consolidate multiple sheets

    WOW! Thats all I can say, and thank you! incorporated this into my actual workbook.

    The only thing is that when I enter a percentage value, say 90, it clears the results instead of filtering my monthly results by that value.

    Choose month, then choose % value - This will show filtered results.

    Is the testing2-1.xls workbook designed to do this?

  10. #10
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Consolidate multiple sheets

    Not sure I follow the error. Do you have a sample data set that includes proper probability data and the macro installed/edited for your layout? I can only see what you're seeing when we're looking at the same things.

    If the probability cell is being ignored, then the data in that cell isn't matching with the correct column on the sheets being filtered. Make sure it is filtering by the correct column and the types of data are the same.

  11. #11
    Registered User
    Join Date
    10-20-2009
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    58

    Re: Consolidate multiple sheets

    Apologies as my response was not clear.

    In testing2-1.xls Sheet 'Europe(Need)', columnH contains percentages values

    If I enter 90 or 90% in cell L8 of Sheet 'GlobalSummary', it clears all of my results in this 'GlobalSummary'

    I have changed the column H in 'Europe(Need) to a number value from percentage, this now works.

    Thanks,

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1