+ Reply to Thread
Results 1 to 6 of 6

List of items from another worksheeets

  1. #1
    Registered User
    Join Date
    08-24-2006
    Posts
    61

    List of items from another worksheeets

    i am consolidating the list of items written by another person in worksheet A and would like to list them to my excel sheet as summary.
    example:
    excel 1
    Worksheet A
    column C5:F5 (merged cells) "Title"
    column A1 "Number" (header) , from A10:A100 there could have blank or with value.
    column B1 "Name" (header) , from B10:B100 there could have blank or with value.

    excel 1
    Worksheet B
    column C5:F5 (merged cells) "Title"
    column A1 "Number" (header) , from A10:A100 there could have blank or with value.
    column B1 "Name" (header) , from B10:B100 there could have blank or with value.

    At my worksheet:
    Table column B10 "Title Description" to list the value written in Worksheet A C5:F5
    Table column D10 "Item Number" to list all value written in Worksheet A A10:A100 , skip the blank.
    Table column E10 "Item Name" to list all value written in Worksheet A B10:B100 , skip the blank.
    Then the same capture the excel 1 Worksheet B, here i am not really sure how to make it as the worksheet A value can be varies and how to make my worksheet auto insert value from Worksheet B when all value in worksheet A has been captured. something like auto append next row.

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,038

    Re: List of items from another worksheeets

    It's probably not a good idea to have merged cells.

    Post a sample spreadsheet with expected results this will save people having to duplicate the data and provide a quicker solution, remove any sensitive data, create a mockup example if necessary.
    Use the Go Advanced option at the bottom of the page then scroll down to Manage Attachments as the "paperclip" method does not work on this forum.
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Registered User
    Join Date
    08-24-2006
    Posts
    61

    Re: List of items from another worksheeets

    okay, i have modified the question and attached the file.

    Worksheet A1
    column C5:F5 (merged cells) "Title"
    column A8 "Number" (header) , from A9:A2000 there could have blank or with value.
    column B8 "Name" (header) , from B9:B2000 there could have blank or with value.

    Worksheet A2
    column C5:F5 (merged cells) "Title"
    column A8 "Number" (header) , from A9:A2000 there could have blank or with value.
    column B8 "Name" (header) , from B9:B2000 there could have blank or with value.

    At my worksheet:
    Table column B10 "Title Description" to list the value written in Worksheet A1 C5:F5
    Table column D10 "Item Number" to list all value written in Worksheet A1 and A2, A10:A2000 , skip the blank.
    Table column E10 "Item Name" to list all value written in Worksheet A1 and A2, B10:B2000 , skip the blank.
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,588

    Re: List of items from another worksheeets

    This proposed solution employs some helper columns on Worksheets A1 and A2 that filter out the blanks.
    The array entered formula* that populates the helper columns is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Note: If your profile had specified the version of Excel you are using, it may be possible to use AGGREGATE(... instead of SMALL(IF... so this would not be array entered.
    The formula that populates the 'Title' column is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The formula that populates the Item Number and Item Name columns is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    *Array entered formulas are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    08-24-2006
    Posts
    61

    Re: List of items from another worksheeets

    Quote Originally Posted by JeteMc View Post
    This proposed solution employs some helper columns on Worksheets A1 and A2 that filter out the blanks.
    The array entered formula* that populates the helper columns is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Note: If your profile had specified the version of Excel you are using, it may be possible to use AGGREGATE(... instead of SMALL(IF... so this would not be array entered.
    The formula that populates the 'Title' column is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The formula that populates the Item Number and Item Name columns is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    *Array entered formulas are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    Let us know if you have any questions.
    Thanks JeteMc !

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,588

    Re: List of items from another worksheeets

    You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools drop down in the ribbon above your first post. 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. Vba macro help on copying specific worksheeets in a master workbook
    By CheenaS in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-28-2018, 04:39 PM
  2. [SOLVED] To compare a list of items in column ? with items in all columns that are not blank.
    By JamesJohnson31 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-03-2015, 03:32 PM
  3. Replies: 1
    Last Post: 09-03-2012, 10:03 PM
  4. Using auto filter in protected worksheeets
    By EdMac in forum Excel General
    Replies: 2
    Last Post: 05-10-2007, 04:42 AM
  5. Replies: 1
    Last Post: 06-24-2005, 12:21 AM
  6. Items in disabled items list - unknown excel addins causing probs
    By Rich in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-16-2005, 06:06 PM
  7. [SOLVED] selection of several worksheeets
    By Enrico in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-09-2005, 10:06 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