Looking for easy data entry to output monthly calendar view

I have a 10 groups in my organization that have various initiatives throughout the year but we need a view to everything going on at a glance.
The inputs would be the 10 group names within the business, their initiatives, start and end dates.

I've seen awesome calendars in this forum that show each day within the month, but that's too granular for us. We are seeking a higher level view, perhaps by quarter, that shows what's going on in each month. The information written on the calendar needs to include the name of the group and the info in the initiative column. I've attached a slide simply with the inputs.

I don't know if I did a very good job explaining this so feel free to ask me questions!
Thanks for your time!
Z-

It's hard to say what would work for you without a template. You might be better served finding and posting a template you like, then seeing what can be done with it. If you're open to something macro enabled, take a look at the attachment - a template I've found popular with users. It isn't quarterly, exactly, but it does fit rather nicely on one screen. It uses a small VBA procedure:

Please Login or Register  to view this content.

...to track the selected date, and it updates the table to the right with the projects that are active on the date you've selected. It auto-updates with each click, and you can add more initiatives to your list and they'll be automatically included. The updating table to the right uses the formula below in AN3, array entered with Ctrl + Shift + Enter, then filled right and down:

=IFERROR(INDEX($AI$3:$AL$33,SMALL(IF(($AK$3:$AK$33<=selectedCell)*($AL$3:$AL$33>=selectedCell)=1,ROW($AI$3:$AI$33)),ROW(1:1))-2,COLUMN(A:A)),"")

This formula is designed to look at up to 30 entries in your initiatives list. If you need more, just change all of the "$33"s in the formula to something bigger.

People seem to like the interactivity of this template, and there are ways to doll it up with conditional formatting, additional info, etc. Take a look, or peruse Excel's templates to see if there's something you like. It's tough for us outsiders to know what kind of look would work for your business.

Thanks cantosh - I found a template on the site and started to tweak it - see attached file. But we're looking for a quarterly view.


See attached. If the original template is needed (in the event I messed up a formula - the link to it is here: http://www.excelforum.com/excel-form...ml#post4439479

file ef939a_Bookings and Hires.xlsx‎

This is great how could I change it to look at a range of dates? Like say an entire month for a workload schedule?

How could you make this show a range of dates in the active projects section? What would that formula be?

@Monica: If you're referring to my calendar attachment, you would tweak the conditions in the middle of the formula to replace the selectedCell with your date range cells. For instance, if you enter your search start date in AR2 and your search end date in AS2, then I believe:

=IFERROR(INDEX($AI$3:$AL$33,SMALL(IF(($AK$3:$AK$33<=$AS$2)*($AL$3:$AL$33>=$AR$2)=1,ROW($AI$3:$AI$33)),ROW(1:1))-2,COLUMN(A:A)),"")

...in AN3 should work. Be sure to confirm it with Ctrl + Shift + Enter, then fill right, then down. Since you're no longer using the selectedCell feature, you could even delete the macro and save it macro-free. Frankly, you could ditch the calendar 'image' altogether, though you may want to keep it and add some conditional formatting.

@zblackwell: I'm glad you found one you like! Is there a particular functionality you're still looking to add, or are you good to go?

@cantosh - I appreciate your replies! Yes additional functionality is needed:

- Am seeking a quarterly view, not monthly
-need to expand to accommodate all 10 groups
-need to show both the group name AND the initiative details on the calendar

let me know if this doesnt make sense

From a data standpoint, creating filters by date usually isn't too tricky, but I'm not 100% sure I'm understanding what you're picturing on the visual side of things: 20 cells within each date (10 groups plus their initiative details) across 90 dates? Won't it end up being too large to be recognizable as a calendar?

@cartosh - from a visual side, I don't think it has to be 20 cells, I could just be 10 cells. if we include group name and initiative detail in the same cell. it would just mean it cant be a drop down like the existing template.

Experiment a bit with the attached, and see if it's feasible. I've added a helper column and expanded your groups to 10. The helper column is column I and it includes the text to be entered on your calendar using the following formula in I2, filled down:

=IF(F2="","",F2&IF(INDEX(N:N,E2)="","","-"&INDEX(N:N,E2)))

A helper column wasn't quite required, but it simplifies things a bit. From there, I tried to expand your calendar template to 10 rows per day, then I triplicated the calendar and offset the two new ones, so when you select your month, you should get that month's returns as well as the ensuing 2 months. It appears to work for me, rolling from month to month and year to year smoothly, but you'll want to play around with a few sample entries. Stretching and tripling the calendar element screwed up most of the conditional formatting that provided the pretty colors, so that'll need to be redone, but the CF formulas were pretty basic "If not blank, color it in" formulas. Take a look and see what you think...