Auto-Populate Excel Calendar from Project List

Hello Experts!

I have come across your site through Google searches, and I need your Excel expertise to help me refine a few formulas to auto-populate an Excel Calendar from a Project worksheet.  I have adapted an MS template and have used multiple resources to guide me, but I am stuck adapting some of the resources to accommodate multiple dates.

Basically, I would like the dates entered in the "Project" sheet to populate in the "Calendar" with the ProjectName and StatusHeading from the Project sheet.  Also, If possible, I would like to avoid VBA and macros since the users are not always wise enough to enable them when the warning pops up on open.

Here are my issues on the Calendar:
1) I can't get any dates beyond Projects!C:C (25%) to show up.  I get an N/A error.
2) I got an error when referencing the StatusHeading in the formula so I have created a nested IF.  Hopefully, there is a better way.
3) I would like to copy the formula to Rows 8-11, etc. that adds something like IF B7=0,"", (ELSE I'm not sure how to write this part but when B7 and B8 match, then go to the NEXT 11/1/2015 date OR show the different new date OR leave blank if the previous criteria are not met)

On the Projects sheet:
4) I would like to write a conditional formatting formula that if the same date shows up more than 5 times, THEN the text turns red so the user knows it won't fit on the calendar.

Any help is appreciated. THANK YOU!

Here are some other resources I used to perhaps help: (excel_help1=Test1 Excel Calendar.xlsx) (ef89_scheduler.xls)
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Russell FoxDatabase DeveloperCommented:
Sorry, but I've been staring at this and I don't see it happening without VBA. Excel's not good at "find all the instances of", but it is good at "find the first instance of", so trying to match a list of dates like this will be hard without a VBA loop. I totally get not wanting to use macros - the security warning scares people, as it normally should.
I think Russell is correct as long as your lookup table is in its original structure. If you can be flexible about the structure then the attachment is a possible solution.

A few things to note. I changed some  January dates from 2015 to 2016. Also, be careful with the values you're looking up. Project 3 had a trailing space in it so my initial formulas resulted in errors. I changed the table structure to a vertical format and added a column with a unique value (for lookup purposes). Without this unique value the problem got to be a bit mind bending. Hopefully this is okay for your purposes.

The formula looks pretty intimidating but you'll notice that it's one formula that repeats for each project, with only the project number changing. Char(10) just adds a hard return. If you prefer you could probably change this to: CHAR(44)&CHAR(32). That's a comma followed by a space.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TechNinja2Author Commented:
Doug - Brilliant!  My apologies on the delay in response.  I'm new to this process and thought I would have more time to test formulas and respond before my question was tagged as abandoned.  

You helped me get in the direction I wanted.  I am beta testing putting each project in its own cell so I can conditionally format (highlight) each project.  I have kept your formula in the first blank cell of a calendar date and have created a new formula for the remaining blank cells of a calendar date.  I have still have a few tweaks, but I thought I'd assign points while I can.

Thanks again!
I'm glad it was helpful. Best of luck!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.