Page 2 of 5 FirstFirst 1 2 3 4 ... LastLast
Results 11 to 20 of 45

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

  1. #11


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

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


    Here is the worksheet. https://drive.google.com/file/d/0B2c...it?usp=sharing

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


  3. #13
    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!!

  4. #14
    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
    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")


  5. #15
    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

  6. #16

    Wink

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

Name:	Capture12.PNG 
Views:	4 
Size:	11.1 KB 
ID:	2566  

  7. #17
    Administrator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,655
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Take a minute to rad http://www.excelguru.ca/content.php?184 about cross-posting

  8. #18
    I have closed out all others. NBVC has been the only one to help. ~Thank you
    Last edited by NBVC; 2014-08-20 at 01:06 PM. Reason: corrected spelling of my handle

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


  10. #20

    Red face

    Yea, I caught that ...sorry!

    When I enter the correct condition, I get the following error as seen on the screenshot attached.
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	Capture13.PNG 
Views:	4 
Size:	26.9 KB 
ID:	2578  

Page 2 of 5 FirstFirst 1 2 3 4 ... 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
  •