Page 3 of 5 FirstFirst 1 2 3 4 5 LastLast
Results 21 to 30 of 45

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

  1. #21


    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	Capture14.PNG 
Views:	5 
Size:	20.2 KB 
ID:	2579  
    Last edited by Turtle; 2014-08-21 at 03:38 PM.

  2. #22
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,509
    Articles
    0
    Excel Version
    Excel 2016
    You picture isn't telling me much.


  3. #23

    Wink

    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/0B2c...it?usp=sharing

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

  4. #24
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,509
    Articles
    0
    Excel Version
    Excel 2016
    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... ?


  5. #25
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,509
    Articles
    0
    Excel Version
    Excel 2016
    Are you okay with helper columns in both sheets?


  6. #26
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,509
    Articles
    0
    Excel Version
    Excel 2016
    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.
    Attached Files Attached Files
    Last edited by NBVC; 2014-08-21 at 08:13 PM.


  7. #27
    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!!

  8. #28
    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??

  9. #29
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,509
    Articles
    0
    Excel Version
    Excel 2016
    Quote Originally Posted by Turtle View Post

    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??
    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.


  10. #30
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,509
    Articles
    0
    Excel Version
    Excel 2016
    Quote Originally Posted by NBVC View Post
    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.
    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.


Page 3 of 5 FirstFirst 1 2 3 4 5 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •