+ Reply to Thread
Results 1 to 10 of 10

Display List Of Open Items

  1. #1
    Forum Contributor
    Join Date
    01-08-2013
    Location
    Jacksonville, Fl
    MS-Off Ver
    Excel 2016
    Posts
    355

    Display List Of Open Items

    Hello everyone,

    I want to create a workbook that will track "Open" items that are past the due date from project events. I attached a sample workbook to better explain my intentions. There are 4 worksheets. 3 of the worksheets are for different events and 1 worksheet (Summary) to display all open items (Event Name, Task, Status, Date, Responsible Person, and Manager) from all three events. Not all the open items need to be listed, only the ones that are currently past due.

    I'm hoping to have an easy way to do this. I'm not sure if I have to use a macro or not. If so, one important factor is that I will be adding new Event worksheets every week and I will need the macro to search them as well.

    I hope my explanation made sense. Please take a look at my sample for clarification.

    Thank you in advance to anyone willing to take a look at this for me.

    - Justin
    Attached Files Attached Files
    Last edited by Justair07; 08-28-2014 at 03:27 PM.

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,846

    Re: Display List Of Open Items

    Hi Justin,

    There is an old tool called the Pivot Table Wizard that "consolidates" regions into a single pivot table. See the attached where I've tried to see if it would solve your problem.

    See how to add this wizard to your QAT at:
    http://www.pivot-table.com/2010/07/1...in-excel-2007/

    Hope this helps.
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Forum Expert boopathiraja's Avatar
    Join Date
    07-12-2013
    Location
    Coimbatore,TamilNadu, India
    MS-Off Ver
    Excel 2007, 2013, 2016, 365
    Posts
    1,455

    Re: Display List Of Open Items

    @ Marvin is there any way to change the data source like standard pivot table ?
    Click just below left if it helps, Booşathì

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,846

    Re: Display List Of Open Items

    Using the Consolidation Wizard you add the sources of the data.
    See an example of what I mean at
    http://blog.executivetrainingsolutio...ltiple-ranges/

  5. #5
    Forum Contributor
    Join Date
    01-08-2013
    Location
    Jacksonville, Fl
    MS-Off Ver
    Excel 2016
    Posts
    355

    Re: Display List Of Open Items

    Quote Originally Posted by MarvinP View Post
    Hi Justin,

    There is an old tool called the Pivot Table Wizard that "consolidates" regions into a single pivot table. See the attached where I've tried to see if it would solve your problem.

    See how to add this wizard to your QAT at:
    http://www.pivot-table.com/2010/07/1...in-excel-2007/

    Hope this helps.
    Hi Marvin,

    How do I change the Number Format. For example I want the Date to be a Date and Names to be Text. It seems to be as if you can only set one type of format.
    Attached Images Attached Images
    Last edited by Justair07; 08-28-2014 at 04:13 PM.

  6. #6
    Forum Contributor
    Join Date
    01-08-2013
    Location
    Jacksonville, Fl
    MS-Off Ver
    Excel 2016
    Posts
    355

    Re: Display List Of Open Items

    After doing more research I've realize I need a macro lol

  7. #7
    Forum Contributor
    Join Date
    07-18-2012
    Location
    New York
    MS-Off Ver
    2016
    Posts
    191

    Re: Display List Of Open Items

    I'm sure there's a better way to do it but here's something that I put together based on something I already had.

    Delete the blank summary tab you have, insert in Column A on each tab a column for "Event", then run this and delete anything that's already closed which should sort to the top and you'll be left with a list sorted by "Event" then by "Due Date".

    One problem that I came across that maybe someone else can help with is how to get the range to be a dynamic selection so in the event that you go passed a certain number of lines it will still pick it up. I'm sure there's also a way to get it to delete anything that has data in the date closed column for you but this is hopefully a start that get's you moving in the right direction.

    Please Login or Register  to view this content.

  8. #8
    Forum Contributor
    Join Date
    01-08-2013
    Location
    Jacksonville, Fl
    MS-Off Ver
    Excel 2016
    Posts
    355

    Re: Display List Of Open Items

    Unfortunately the code doesn't quite do the trick but I appreciate you sharing. Hopefully someone will come along with a different approach.

  9. #9
    Forum Contributor
    Join Date
    01-08-2013
    Location
    Jacksonville, Fl
    MS-Off Ver
    Excel 2016
    Posts
    355

    Re: Display List Of Open Items

    Hello everyone, just wondering if anyone with some VBA expertise had a chance to look at this problem for me? I would greatly appreciate any help I can get.

  10. #10
    Forum Contributor
    Join Date
    01-08-2013
    Location
    Jacksonville, Fl
    MS-Off Ver
    Excel 2016
    Posts
    355

    Re: Display List Of Open Items

    Okay here is what I have so far:
    Please Login or Register  to view this content.
    It works as far as coping the data is concerned but I need the macro to check all the worksheets in the workbook, not just the one that is defined. New workbooks will be created all the time.

  11. #11
    Forum Contributor
    Join Date
    01-08-2013
    Location
    Jacksonville, Fl
    MS-Off Ver
    Excel 2016
    Posts
    355

    Re: Display List Of Open Items

    Okay I figured it out. Here it is if anyone else might be able to benefit from it.
    Please Login or Register  to view this content.
    Thanks self!

+ 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. [SOLVED] code to open/close userform based on if a list box contains list items or not
    By Nitefox in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 2
    Last Post: 01-11-2014, 06:23 PM
  2. Replies: 1
    Last Post: 09-14-2013, 04:55 AM
  3. Trying to create a formula that will display items in a clean list
    By jnlong in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 04-29-2013, 07:51 PM
  4. Replies: 4
    Last Post: 09-21-2010, 06:24 PM
  5. Combo Box - Display Unique Items in List
    By Gary S in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-20-2007, 11:15 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