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

This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.


Thread Starter
Jan 11, 2013
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
Due Date
Completed Date

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
Follow up

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.


This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Members online