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

Turtle

New member
Joined
Aug 19, 2014
Messages
29
Reaction score
0
Points
0
Hello Excel Experts!

I need to get the Event Name to show in the weekly tab during the time of the event along with, if possible, the start and end times.

I currently have a COUNTIF in there for the times, but I do not know how to let it give the Event Name from the Event List. This is what I currently have:

=COUNTIFS(CSEvents[Date of Event], $C20, CSEvents[Start Time], "<="&N$6, CSEvents[End Time],">="&N$6,CSEvents[Calendar Entry],INDEX($M$2:$M$5,MOD(ROWS($1:19),6)))*N$6

I have also played around with the formula below, but it does not give the start and end times, nor the name:

{=IFERROR(INDEX(CSEvents,MATCH(TEXT($C41+O$6,"mmddyyyhh")&$M$5,TEXT(CSEvents[[Start]:[Start]],"mmddyyyhh")&CSEvents[[*Kind of Event]:[*Kind of Event]],0),2),IFERROR(INDEX(CSEvents,MATCH(TEXT($C41+O$6,"mmddyyyhh")&$M$5,TEXT(CSEvents[[Finish]:[Finish]],"mmddyyyhh")&CSEvents[[*Kind of Event]:[*Kind of Event]],0),3),""))}

Suggestions?
 
Can you post a sample workbook showing examples of expected results?

Also, if you have posted the question in other forums, please advise and show links to those threads here.
 
Hi,

It wont let me share the link so I have attached the file.


Thank you for your time!
 

Attachments

  • Copy of Calendar_Project3.xlsm
    63.9 KB · Views: 94
I have tried excelforum.com and mrexcelforum.com and nobody has responded yet. I posed on 8/14 to both. Thought I would try here too so see if anyone here could help. Ealier this month I have also tired microsoft community, but nobdy there knew how to do it. Can it be done??
 
The result of the COUNTIFS() portion seems to always be 1. Is that true? Meaning there is always only one row that matches all the criteria in the CS Events List sheet?
 
If so, try for example to get the Event for the result in E10 of Weekly Calendar:

=IFERROR(INDEX(CSEvents[Event Name],MATCH(1,INDEX((CSEvents[Date of Event]= $C10)*(CSEvents[Start Time]<=O$6)*(CSEvents[End Time]>=O$6)*(CSEvents[Calendar Entry]=INDEX($M$2:$M$5,MOD(ROWS($1:9),6))),0),0)),"")

P.S. Looks like this are your links: http://www.excelforum.com/excel-formulas-and-functions/1031557-can-you-help-need-to-add-to-an-array-formula.html
[url]http://www.mrexcel.com/forum/excel-questions/799210-complicated-calendar-project-array-formulas.html



[/URL]
 
Last edited:
Thank you! But when I enter that into a cell and copy it over to the row it shows the begining of the event name(more than what I could do!) but then the color for the event category and the time are gone.
 
I also need to add rows to each day so that there can be up to 7+ events per day to see how they are laid out by hr on the weekly. but when I copy and past the formula down to the added rows, the new events do not show. Can you help with this as well? Sorry, and thank you!


*For the previous message, would I just need to add the conditional formatting again for the color categories to show?
 
Last edited:
*For the previous message, would I just need to add the conditional formatting again for the color categories to show?

probably....

Try posting back the workbook showing samples of expected results, and if not obvious, how they were achieved.
 
Row 14 has the formula in there which you suggested. It gives the Event name at 8am and 2pm and at but when I Copy it to row 15 the event that was there from 5-730pm is absent and it states the same event name as row 14. What I am going off is the Presentation doc attached as well. Essentially I would love for it to list the events like this, but again I need to have 7+ events for each day to show, wether it be 4 meetings and 2 product launches and 2 others or antoher combination of them with the max at 7...but could be expanded to more if need be.

Thank you really for your time and advise!

Presentation-What I am kinda going off of..that I would like. https://drive.google.com/file/d/0B2cIkkORjHDDOVlVN2xfNjgtWVE/edit?usp=sharing


Here is the worksheet. https://drive.google.com/file/d/0B2cIkkORjHDDVVFUNm9ST25wSnc/edit?usp=sharing
 
In E7 enter formula:

=IFERROR(INDEX(CSEvents[[Event Name]:[Event Name]],MATCH(1,INDEX((CSEvents[[Date of Event]:[Date of Event]]= $C7)*(CSEvents[[Start Time]:[Start Time]]<=E$6)*(CSEvents[[End Time]:[End Time]]>=E$6)*(CSEvents[[Calendar Entry]:[Calendar Entry]]=INDEX($M$2:$M$5,MOD(ROWS($1:2),6))),0),0)),"")

copied across and down the whole table.

Is that the information your require?

Unfortunately, though you can probably hide the duplicating text in each row to only show the first (using conditional formatting), but in that first cell you won't see the entire text string unless the column widths are set "to fit".
 
Nope, that is great! I can work with this! TY!!! Now to change the number of rows entered I would just need to change the ($1:2),6))),0),0)),"") in the formula, and enter more rows> copy down? Also, is there a way with the conditional formatting to have which various events on each day. I think currently it is set to 1 "color" to each row...verses having an input of which ever one is there..does that make sense?

So if there is there are 3 meetings and 4 product launches for example, they would show like that in that specific day, verses currently it would only show 1 of each.

Would I just need to add more rules in there with IF's and AND's with the variouse colors?

Really thank you very much!!
 
Nope, that is great! I can work with this! TY!!! Now to change the number of rows entered I would just need to change the ($1:2),6))),0),0)),"") in the formula, and enter more rows> copy down?


Yes, I think that should be right.

Also, is there a way with the conditional formatting to have which various events on each day. I think currently it is set to 1 "color" to each row...verses having an input of which ever one is there..does that make sense?

So if there is there are 3 meetings and 4 product launches for example, they would show like that in that specific day, verses currently it would only show 1 of each.

Would I just need to add more rules in there with IF's and AND's with the variouse colors?

I have no idea what you are asking here. If you are trying to match colour coding that is in your M2:M5, then you will need 4 separate conditional formats (one for each colour) in each cell, which use the INDEX part of the formula above as a check...

eg..

=AND(Condition1,INDEX($M$2:$M$5,MOD(ROWS(1:7),6))="Training")
 
NBVC, Thank you SO incredibly much! Really! You ROCK!!

yes that was what I was trying to get to with the conditional formats question...sorry for being unclear!

Let me give this a try and I will let you know :)
 
So the conditional formatting did not work, but it is probably user error! PEPKAK! Attached is a screen shot of the format I used. Before when the colored bar was there These were the rules:

AND(E14, OR(NOT(D14, NOT(F14)))...format with custom number> h:mm A/P

=OR E14.....format> fill> color needed.


Should I also include the first rule besides the colored options? Should the color rule be AND(OR?
 

Attachments

  • Capture12.PNG
    Capture12.PNG
    11.1 KB · Views: 7
I have closed out all others. NBVC has been the only one to help. ~Thank you :)
 
Last edited by a moderator:
your screen shot appears to show that you use the formula exactly as I had shown in my example... the part, condition1 was meant to be replaced by you with the actual conditional check, along with the check with M2:M5
 
Yea, I caught that ...sorry!

When I enter the correct condition, I get the following error as seen on the screenshot attached.
 

Attachments

  • Capture13.PNG
    Capture13.PNG
    26.9 KB · Views: 8
Back
Top