Do I use a COUNTIF or a IFERROR(INDEX(MATCH formula??

Got the conditional formatting to be accepted, but the "colors" still do not show. I did a rule for each "color"/category and placed and AND for the first one and the other 3, I did OR in front. Should it be all AND's or all OR's?

Also, there are 2events showing on the same row, I am okay with that except for the first line there, the events overlap eachother. What could be done to it = 1 event per row?...><??

See attachment

You picture isn't telling me much.

I have only inserted the formulas and conditional formatting into the Sunday rows. The updated version of the worksheet is below for your review.

https://drive.google.com/file/d/0B2cIkkORjHDDa2kweTdfb2R2amM/edit?usp=sharing

I am sorry for all the questions. Thank you for your help once again!

Ok, I think there is some confusion here.

in the weekly events sheet, with the formula you have you can only have one row for each event (i.e only 4 rows per day). If the same event occurs more than once in a day you will get them both in one row (and you may get overlap).

I am not exactly sure how to get it so that you can have one separate event per row. I will need some time to think it through. I am not sure that is even how you had it originally.

As far as the conditional formatting is concerned. I am not sure why you have the words Training, Meeting, etc as one of the conditional checks... ?

Are you okay with helper columns in both sheets?

Ok, have a look at the attachment.

I have added a helper column in the CS Events List sheet. This concatenates the date with the number consecutive count of that date, using this formula (this column can be hidden after). This will help us separate an event per row, so you should not get two events in any one row:

=B4&"_"&COUNTIF(B$4:B4,B4)

copied down.

In the Weekly calendar, I have expanding the daily number of rows to 8. This will accomodate for a blank first row and 7 potential independent events (1 per row)

The formula in E8 at the top left corner is:

=IFERROR(INDEX(CSEvents[[Event Name]:[Event Name]],MATCH(1,INDEX((CSEvents[[Date of Event]:[Date of Event]]= $C8)*(CSEvents[[Start Time]:[Start Time]]<=E$6)*(CSEvents[[End Time]:[End Time]=]=>=E$6)*(CSEvents[[Helper]:[Helper]]=$C8&"_"&MOD(ROWS($A$8:$A8),8))*(ISNUMBER(MATCH(CSEvents[[Calendar Entry]:[Calendar Entry]],$M$2:$M$5,0))),0),0)),"")

copied across and down the whole table.

For the conditional formatting, I used another helper column in Weekly Calendar sheet to identify the associated event, based on the helper I used in the CS Events sheet and respective row number we are in...

Helper formula here is, starting in AE8 (this column can be hidden after):

=INDEX(CSEvents[Kind of Event],MATCH($C8&"_"&MOD(ROWS($A$8:$A8),8),CSEvents[Helper],0))

Now the separate conditional formats are simple:

For Other (Pink): =AND(E8<>"",$AE8="Other")
For Meeting (Purple): =AND(E8<>"",$AE8="Meeting")
For Product Launch (Blue): =AND(E8<>"",$AE8="Product Launch")
For Training (Green): =AND(E8<>"",$AE8="Training")

Hope this meets with your approval.

I guess I dont really know what you mean by helper columns. If you mean ones that can be hidden...yea I would be open to that!

I am sorry for the confusion. So I would need a specific row to be dedicated to one event type, correct? What if there is 3 events that are catagorized as "Other" for 1 day?...what if there were none? The thing is I can not predict how many of each event type will be entered each day. I just would like to limit the number of events to 7 and have multiple events listed during their given time frame...eachone on a seperate row.

The code that was present before is still in cell E16 and on. I love the event titles showing!!..just need 1 event per row, with the correct cooresponding color....also if it can be done to leave the start and end times as well. IF it can not be done it is alright.

To pick your brain further....(I am really afraid I am going to scare you off!!) they now want to be able to select a date from a mini calendar on the side in addition to being able to enter the date(WEEK OF) ..I was thinking in U1:AB5.....but I do not have the calendar form control, nor do I have the access to download it. Is there a VBA code that you know of that would "link" it to the spreadsheet as well?

So sorry!!

That is awesome! You are just amazing!! Lets sit on that a min!



Any ideas on how to get the name to show only in the first cell of the event time and in no other?!..so that the event name spreads across??

Turtle said:

Any ideas on how to get the name to show only in the first cell of the event time and in no other?!..so that the event name spreads across??

Click to expand...

I mentioned at the beginning that would not be easy because I have to drag a formula across, and even if I get it to return a formula blank, the fact that there is something physical in those cells won't allow the text to splash into adjacent cells.

NBVC said:

I mentioned at the beginning that would not be easy because I have to drag a formula across, and even if I get it to return a formula blank, the fact that there is something physical in those cells won't allow the text to splash into adjacent cells.

Click to expand...

One thing I could suggest is to have blanks in all the cells and have just the colour coding to identify the time span and event type. Then in a separate column at the beginning or end of the table, identify the Event Name (since there will only be one event per row, it would work.

I will try that too, and see what I can come up with! Thank you again for everything! I have really learned so much! I am truly greatful!

Alright, for some reason I can not get it to work. I have tried many different formulas to not get the name to appear in every cell, just the first of the event. Can you show me an example of what you were talking about?..It could be I am thinking of it the wrong way?!

**I am also trying to insert a calendar that will work with the "Week of" box. The users would like to have the choice to enter a date(current version) and also have the option to see the calendar and select a date from there which will then enter the date selected to the cell (Q4), which will then pull the corresponding week in the worksheet. Does that make sense?

When trying to do the date/time picker, it gives me a cell from which I can have the drop down calendar appear, but I do not want that option. Can you help with this as well?

Thank you again!

For the users of the calendar project, they run on a 32bit of Windows 7. I can create the calendar running of home computer which runs 64bit for Windows; (once I fugre out how). Once created, how do I get the others to see it, without having them install stuff on their computers??!!

This is what I was talking about...

I put a formula in column D

=IFERROR(LOOKUP(2,1/(E8:AD8<>""),E8:AD8),"")

copied down, which extractts the event description.

Then I revised the conditional formats so to format the font colours to match the background colours, making them invisible.

I also formatted the "helper" column AE with custom format type: ;;; to hide the text.

And I formatted column D to get the corresponding colour coding for easier tracking (but you can remove that if you want).

As far as the calendar is concerned. It is not really sure what you are after, but I would suggest you have a drop down list that points to a list you have somewhere of all the dates you want to choose from it might make it easier and you shouldn't need to worry about versions, etc.

Hi NBVC,

After digging into this, I really like this version! I really appreciate all of your help throughout this project! I know I have said it before, but, YOU ROCK! Thank you again for the time spent with me.

You are welcome. And thanks for the positive feedback. It is appreciated

Can I ask you one more questin

I need to find the Duration time and currently have have it so you enter the Event date and start/end times. Easy! But now they have trainings I want to show go for 2 weeks, so I have added a Start Date and End Date along with the Start time and End time. How would I calculate this to show in 1 cell in the Events List as either the number of days or if only 1 day to show how long in h:mm??

Then to get this to translate to the weekly and monthly calendar, how would this work with the formulas?...just add [Start date:start date], [End Date:end date] to the existing formula??


We are open from 8am-12am Sunday -Saturday.

~Thanks again! *Cheezy grin*

Not sure what you mean. You have a Duration column already which shows the difference. No?

yes, it is "hidden" in the CS Events in column J. but only shows for the duration in hours and minutes currenly.

Would this formula work?

=(NETWORKDAYS([START DATE OF EVENT], [END DATE OF EVENT])-0)*(24:00"-"8:00")+IF(NETWORKDAYS([END DATE OF EVENT],[END DATE OF EVENT]), MEDIAN([END TIME], "8:00", 24:00"), "24:00")-MEDIAN(NETWORKDAYS([START DATE OF EVENT],[START DATE OF EVENT])*START TIME, "8:00","24:00")