+ Reply to Thread
Results 1 to 20 of 20

Filter data from a master to multiple sheets + appending lists

  1. #1
    Registered User
    Join Date
    11-08-2013
    Location
    Boston, MA
    MS-Off Ver
    Excel 2007
    Posts
    17

    Filter data from a master to multiple sheets + appending lists

    I'm trying to streamline a workbook so that all data is entered in one spot that then auto-populates several other sheets with filtered results, based on the criteria of lists that may change over time.

    Using the example I attached:
    - The user enters line items in the master. Type is selected from a drop-down list. That drop-down needs to populate from three separate lists on a different sheet.
    - Then, depending on the type of a record, each record then populates three other 'result' sheets.
    - Ideally, each 'result' sheet should update automatically as the user enters results on the Master sheet. Likewise, the Type drop-down list should be able to change as items are added to it.

    I've included my example. Please feel free to ask me any questions if you think you can help:

    Meal Schedule.xlsx

  2. #2
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Filter data from a master to multiple sheets + appending lists

    Hi nomwich
    I'd think you'll need some VBA Code to accomplish all of this. Are you open to that? If so, can your Codes Worksheet be structured differently?
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  3. #3
    Registered User
    Join Date
    11-08-2013
    Location
    Boston, MA
    MS-Off Ver
    Excel 2007
    Posts
    17

    Re: Filter data from a master to multiple sheets + appending lists

    Quote Originally Posted by jaslake View Post
    Hi nomwich
    I'd think you'll need some VBA Code to accomplish all of this. Are you open to that? If so, can your Codes Worksheet be structured differently?
    Yes to all of the above. Do I need to change the Codes sheet, or did you have something in mind?

  4. #4
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Filter data from a master to multiple sheets + appending lists

    Hi nomwich

    I'll look at the Code Sheets and make recommended changes.

  5. #5
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Filter data from a master to multiple sheets + appending lists

    Hi nomwich

    This Code is in the attached:

    In Sheet Master
    Please Login or Register  to view this content.
    In Sheet Codes
    Please Login or Register  to view this content.
    To view the Code Right Click on the Sheet Tab ---> View Code.

    The Code in Sheet Master runs when the Value in Column E changes.

    The Code in Sheet Codes runs when you click the Button.

    Any Items you add to Sheet Code will be automatically added to the Drop Down because I've added a Dynamic Named Range that populates the Data Validation for the Drop Down.

    I've changed the Structure of Sheet Codes and I've changed the Column Layout of Sheet Master.

    Let me know of issues...
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    11-08-2013
    Location
    Boston, MA
    MS-Off Ver
    Excel 2007
    Posts
    17

    Re: Filter data from a master to multiple sheets + appending lists

    Holy cow! This is soooooooo cool.

    So, a few points. For each of the individual sheets, I meant for the "Status" column to display the "Type". That's a mislabel on my part, so my fault. Sorry.

    Everything works great. The only thing I noticed is that it doesn't remove items from the individual sheets. Meaning, when you select a type from drop-down list (on a new record or one that's already been entered), it adds the record to the individual list, but if you happen to change it, it adds the item again to the same sheet, or another depending on what's selected. I need for the individual sheets to act as a filter of the master sheet, displaying only that which applies to the sheet ('Fruit' only shows the fruit entries, if there are any to show). Is that a big change? It's bleeping incredible though.

  7. #7
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Filter data from a master to multiple sheets + appending lists

    Hi nomwich

    I've modified the Code somewhat to anticipate multiple Change Events.

    This has been handled, I believe
    when you select a type from drop-down list (on a new record or one that's already been entered), it adds the record to the individual list, but if you happen to change it, it adds the item again to the same sheet, or another depending on what's selected
    It's been handled by adding a Helper Column G to each Sheet. Notice the Formula in Column G. They are all the same EXCEPT for Master. These Column can be hidden (or the Font made white). I selected G because it's available in your Sample File...it can be anywhere.

    I'm not certain all is handled because I'm not certain what this means
    I need for the individual sheets to act as a filter of the master sheet
    But this appears to be happening
    displaying only that which applies to the sheet ('Fruit' only shows the fruit entries, if there are any to show).
    Let me know of issues.
    Please Login or Register  to view this content.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    11-08-2013
    Location
    Boston, MA
    MS-Off Ver
    Excel 2007
    Posts
    17

    Re: Filter data from a master to multiple sheets + appending lists

    What I meant by the individual sheets acting as a filter for the master sheet, is that all of the data entry is done on the master sheet, and then the individual sheets only shows certain records depending on the a criterion. I guess like having three filtered versions of the same table, filtered in different ways (as in one sheet shows fruit, one meat, etc.).

    I'll give this a try and let you know. I'm working on someone else's mac atm, so it'll take me some time.

  9. #9
    Registered User
    Join Date
    11-08-2013
    Location
    Boston, MA
    MS-Off Ver
    Excel 2007
    Posts
    17

    Re: Filter data from a master to multiple sheets + appending lists

    Works great. The only issue I have noticed so far is that if I delete a line item from the master sheet, it does not remove the line item from the filtered sheet. Changing items works perfectly (as in, If an entered record is changed from a fruit to a veggie, then the record will move to the appropriate sheet).

  10. #10
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Filter data from a master to multiple sheets + appending lists

    Hi nomwich

    I'll look at this later today
    if I delete a line item from the master sheet, it does not remove the line item from the filtered sheet
    Have Leaf cleanup duty this morning...

  11. #11
    Registered User
    Join Date
    11-08-2013
    Location
    Boston, MA
    MS-Off Ver
    Excel 2007
    Posts
    17

    Re: Filter data from a master to multiple sheets + appending lists

    I hear ya. It's that time of year.

  12. #12
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Filter data from a master to multiple sheets + appending lists

    Hi nomwich

    Will you want/need the ability to add additional Categories in addition to Fruit, Meat and Veggie?

  13. #13
    Registered User
    Join Date
    11-08-2013
    Location
    Boston, MA
    MS-Off Ver
    Excel 2007
    Posts
    17

    Re: Filter data from a master to multiple sheets + appending lists

    I actually been working on my own version after learning some new tricks. I'll show you as soon as I'm done, but I believe I may have solved my problem(s). I'll show you tomorrow!

  14. #14
    Registered User
    Join Date
    11-08-2013
    Location
    Boston, MA
    MS-Off Ver
    Excel 2007
    Posts
    17

    Re: Filter data from a master to multiple sheets + appending lists

    Alright. So I took some of your code and some other code I found online and have been tweaking it (after learning what I have about these functions). I pretty much have it down. The Update_Lists macro does the filtering. The Update_Statuses updates and sorts the lists that will be used for filtering. I'm still trying to find the best way to create dynamic named ranges for the lists that can then be referenced for the AdvancedFilter's CriteriaRange in Update_Lists. Yes, this is a bit of a departure from what we had originally been working on, but I failed to mention the necessity for separate lists and for the constant updating of the filtered sheets:
    Please Login or Register  to view this content.
    I realize this is long and meaningless without the sheet. I'll post a copy as soon as it's cleaned up. I messed it up a bit trying stuff out. :D

  15. #15
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Filter data from a master to multiple sheets + appending lists

    Keep me posted...I've developed a different approach also...please answer this question
    Will you want/need the ability to add additional Categories in addition to Fruit, Meat and Veggie?

  16. #16
    Registered User
    Join Date
    11-08-2013
    Location
    Boston, MA
    MS-Off Ver
    Excel 2007
    Posts
    17

    Re: Filter data from a master to multiple sheets + appending lists

    Quote Originally Posted by jaslake View Post
    Keep me posted...I've developed a different approach also...please answer this question
    I will not.

  17. #17
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Filter data from a master to multiple sheets + appending lists

    May I ask why not?

    Sorry, I may have misunderstood...You will Not answer the question ... or you won't have the Need to add additional Categories.
    Last edited by jaslake; 11-13-2013 at 04:52 PM.

  18. #18
    Registered User
    Join Date
    11-08-2013
    Location
    Boston, MA
    MS-Off Ver
    Excel 2007
    Posts
    17

    Re: Filter data from a master to multiple sheets + appending lists

    LOL! I will not have the need for additional categories. Or more specifically, I'd rather not even consider the issue of having more categories.

    Quick question though. Is there a way to specify for the range, in the following example, to NOT be "Drop_Down"? (as in, the criteria should be everything that is not identify in the range "Drop_Down")
    Please Login or Register  to view this content.
    Last edited by nomwich; 11-13-2013 at 06:18 PM.

  19. #19
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Filter data from a master to multiple sheets + appending lists

    Hi

    All is out of context and I can't test this but it would be something like
    Please Login or Register  to view this content.

  20. #20
    Registered User
    Join Date
    11-08-2013
    Location
    Boston, MA
    MS-Off Ver
    Excel 2007
    Posts
    17

    Re: Filter data from a master to multiple sheets + appending lists

    I've opted against the error checking sheet. Too much work.

    That being said, is there a way to ensure that .AdvancedFilter copies and pastes formulas contained in cells and not just values?

+ 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. Replies: 28
    Last Post: 08-15-2013, 09:38 AM
  2. Filter data from a Master sheet based on multiple criteria by VBA code
    By judeprem in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-27-2013, 03:27 PM
  3. Pulling Data From Multiple Workbooks/Multiple Sheets into one Master Book
    By LSUARefugee in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-13-2013, 12:37 PM
  4. Replies: 0
    Last Post: 04-19-2013, 05:50 PM
  5. Appending student data from multiple sheets in chronological order on another sheet
    By StudentTeacher in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-22-2010, 10:53 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