+ Reply to Thread
Results 1 to 3 of 3

How to combine dynamic ranges from multiple sheets into one list?

  1. #1
    Registered User
    Join Date
    05-09-2015
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Question How to combine dynamic ranges from multiple sheets into one list?

    I have an excel spreadsheet with, say, 5 worksheets where I'm entering data in basic table form = A1-K1 contains my headers, each row below contains data, on a regular basis I am adding data to the bottom-most empty row, so maybe today there are 4 rows of data, tomorrow 6, etc., and I am doing this across all 5 worksheets all with the same headers.

    What I want to be able to do is combine all this data in a new sheet, so basically be pulling from row 2 until "last row with data" in each sheet, with ability for it to be updating dynamically so as I'm adding new rows to each individual sheet, the "combine" sheet gets longer and includes said new rows. I don't care what order the data is in on the "combined" sheet.

    I've added an attachment that is a sample version of what I'm looking to do to help clarify.

    Is this possible? I've considered some kind of offset formula but I can't figure out a way to pull dynamic ranges from multiple sheets into one and I'm not strong with VBA which I'm guessing might be the only way to do this.
    Attached Files Attached Files

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,447

    Re: How to combine dynamic ranges from multiple sheets into one list?

    There is clearly a reason why you add data to one or other of the worksheets. Why not just add a column to store that reason, possibly using Data Validation? Then you just have the Combined worksheet with the ability to filter on any of the reasons, or, for that matter, any column or combination of columns. No complex merge process, no VBA, and no duplication of data ... which will, effectively, double the size of your workbook.

    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    05-09-2015
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    3
    Sure- so I'm oversimplifying this example. I'm not actually manually entering this data into each worksheet, I have an index/offset formula that is pulling in data from multiple sheets in other workbooks where it is not ordered and headers/columns are not in standard order. So I figured first step would be to get all the data into this workbook in a consistent way, and then last step is getting it all into the same sheet. This is summary data so I'm trying to use it for a different purpose than it exists for in the other workbooks, so I can't change the organization of the data itself since those workbooks are not in my control.

    Thanks for digging into the why- I hope that helps give context!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Create a dynamic list from multiple sheets
    By shbiskup in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-28-2022, 04:04 AM
  2. [SOLVED] Dynamic Ranges from Different Sheets Question
    By nicoan in forum Excel General
    Replies: 8
    Last Post: 02-24-2014, 02:03 PM
  3. Main sheet populated by multiple sheets, dynamic ranges
    By cmack in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-20-2014, 09:14 AM
  4. [SOLVED] How Can I Build A Dynamic List From Multiple Named Ranges
    By GEANZ in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-02-2013, 01:56 AM
  5. VBA to clear multiple ranges from list of sheets
    By y0rk1e72 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-07-2012, 10:12 AM
  6. [SOLVED] Trying to make multiple dependent drop down list with dynamic ranges
    By Gwen@YouWantWhat? in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-01-2012, 12:55 AM

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