+ Reply to Thread
Results 1 to 9 of 9

Consolidating names from multiple sheets to create one dynamic list

  1. #1
    Registered User
    Join Date
    04-27-2021
    Location
    Melbourne
    MS-Off Ver
    Windows 10 Enterprise Office 365
    Posts
    6

    Consolidating names from multiple sheets to create one dynamic list

    Hi guys,

    I'm needing to retrieve names that can range from A2:A100 across 13 different sheets to form a list in column A of the summary page which includes every name from each sheet and updates automatically when new names are added to various sheets. See sample spreadsheet attached. I've only included 4 names per sheet for this exercise but any could have up to 100.

    Thanks!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Consolidating names from multiple sheets to create one dynamic list

    Administrative note
    Please update your profile as necessary to properly reflect the exact version(s) of Excel your question relates to. Members tailor answers based on your Excel version. Your profile does not indicate your version.
    Thanks

  3. #3
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Consolidating names from multiple sheets to create one dynamic list

    Why have names on multiple sheets: recombining data not the best way to use Excel.

    Look at Power query as a possible solution.
    Last edited by JohnTopley; 04-28-2021 at 01:04 AM.

  4. #4
    Registered User
    Join Date
    04-27-2021
    Location
    Melbourne
    MS-Off Ver
    Windows 10 Enterprise Office 365
    Posts
    6

    Re: Consolidating names from multiple sheets to create one dynamic list

    Office 365 on windows enterprise. I need them in different sheets as that is how the data is being supplied from over a dozen different areas. This data will continue to be updated so putting it in one sheet won't work. Whether it's the best way to use excel or not we need to find a formula that can do this.

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Consolidating names from multiple sheets to create one dynamic list

    If the data is in a consistent format i.e all sheets the same, then Power Query is likely to prove the tool to use.

  6. #6
    Registered User
    Join Date
    04-27-2021
    Location
    Melbourne
    MS-Off Ver
    Windows 10 Enterprise Office 365
    Posts
    6
    Quote Originally Posted by JohnTopley View Post
    If the data is in a consistent format i.e all sheets the same, then Power Query is likely to prove the tool to use.
    Ok, thanks! The isn’t in consistent format at the moment but if I can rectify that I’ll have a go at Power Query. I haven’t worked with that before but I’ll watch some content on it.

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Consolidating names from multiple sheets to create one dynamic list

    Here is an example of how Power Query could be used as John suggested.
    1. Convert all ranges into tables.
    Note that only the rows with data are included as the table will expand contract as data is added or removed.
    2. Use the following Power Query Advanced Editor code:
    Please Login or Register  to view this content.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  8. #8
    Registered User
    Join Date
    04-27-2021
    Location
    Melbourne
    MS-Off Ver
    Windows 10 Enterprise Office 365
    Posts
    6

    Re: Consolidating names from multiple sheets to create one dynamic list

    Brilliant! Thank you.

  9. #9
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Consolidating names from multiple sheets to create one dynamic list

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

+ 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. Replies: 9
    Last Post: 10-16-2017, 03:25 AM
  3. Create a dynamic list based on multiple dynamic factors.
    By sabin348 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-07-2015, 11:05 AM
  4. [SOLVED] Consolidating multiple sheets in master tab and create summary sheet
    By Farida in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-14-2014, 02:41 PM
  5. Create a list that sums values of selected names in a dynamic range
    By PAexcel in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-18-2014, 11:23 AM
  6. Using a list of names to create new sheets as a template
    By GreggDavey in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-24-2010, 09:44 AM
  7. Replies: 4
    Last Post: 05-28-2005, 02:05 PM

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