1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel 2007 - Macro to Create Lists on a Sheet Based on Multiple Criteria

Discussion in 'Business Applications' started by OJI, Jan 11, 2013.

Thread Status:
Not open for further replies.
  1. OJI

    OJI Thread Starter

    Joined:
    Jan 11, 2013
    Messages:
    1
    Thanks for reading, first of all.

    I am not the most savvy person when it comes to programming. I can manage my way around formulas pretty well, but when it comes to VBA and macros I am utterly lost. I believe I need a macro to do what I want, but I have no clue where to begin. I have searched forums but what I need is so complicated that I don't know where to look to find it so I thought I would ask for help.

    I have a spreadsheet which tracks projects I am working on. Right now the sheet I am working on is "2013" for example. The sheet has several columns, but the only columns of significance to this macro are:

    Account Name
    Requested Date
    Urgency
    Due Date
    Completed Date
    Request
    Receive
    Complete

    Account and Requested Date are currently manual entries.
    Urgency is a data validation dropdown with: NORMAL, RUSH and OTHER.
    Due Date is a formula which calculates a due date based on the Urgency selected. (Except Other, in which case I override the Due Date manually.)
    Completed Date is also a manual entry.
    The 3 stages are data validations which default to a blank cell and then have a Square Root sign (which looks like a checkmark) as the only other option.

    Now that I have explained the source data, allow me to go into detail what I would like this to do.
    I would like to populate the first sheet of the spreadsheet with seven lists. These lists would, ideally, tell me at what stage each project is at (by account and due date). The lists I have set up are in seven boxes with borders, 5 rows in each box.

    My boxes on Sheet 1 are:

    Request - Late
    Follow up - Late
    Complete - Late
    Request
    Follow up
    Complete
    Ready

    So what I would like the macro to do is:
    Search "2013" for a value in column A (Account Name).
    If column A has an account name, next look at Completed Date to see if it has been completed. If it has a value (is therefore completed) ignore this row.
    If Column A has a name but there is no completed date... now it gets trickier.

    I would like it to check at what stage the project is at. A checkmark means that stage is completed and it can move on to the next stage. For example if all 3 are blank it is on Request. If all 3 are checked, it is Ready.

    So now the macro can tell me at what level each project is. But I also need to know which ones are late.

    If the due date is past today's date, it is late. If the date is today or later it is not late. Also, because these lists can go on and on, I want it to cut off at 5 entries in a particular box by due date. So the first 5 due, show me those. Anything past that ignore.

    As you can see, this is really complicated. I manage so many of this kind of project that I need a faster method to tell which ones are most important. If something is Ready I need to deal with it right now as opposed to letting it sit and miss my deadline. This has actually happened to me before because I lost track and it is really frustrating.

    I know that visual aids help so I am going to attach a sample workbook demonstrating what I do and how I would like it to look.

    If ANYONE has ANY tips on how to get this done it would be IMMENSELY helpful. I'm a bit overwhelmed at work and prioritizing is so important.

    Thanks in advance to anyone willing to try and take a crack at it. Have a great day.

    PS: I posted this on another forum but I have cleaned it up and made it simpler in this thread so hopefully it is actually possible to do now.
     

    Attached Files:

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1084757

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice