+ Reply to Thread
Results 1 to 6 of 6

Date change & TBA when match cancelled. Try Match and index didn't work

  1. #1
    Valued Forum Contributor
    Join Date
    03-20-2011
    Location
    UK
    MS-Off Ver
    Excel 2007/10/16
    Posts
    840

    Date change & TBA when match cancelled. Try Match and index didn't work

    Attachment 135016Hi
    I'm trying to find formula.
    If team cancelled match. I put in TBA or new date in cell E47 to Z58
    What I'm looking for a formulas to work by change date or TBA including formula I already put in with this
    IF(OR(Setup!$C$18="",Setup!$C$20=""),"",IF(G$5="","",IF(INDEX(INDIRECT("'"&Setup!$N$5&"'!$e$23:$ab$59"),Setup!$M11,G$5)=0,"",INDEX(INDIRECT("'"&Setup!$N$5&"'!$e$23:$ab$59"),Setup!$M11,G$5))))



    Any idea
    Last edited by micope21; 01-06-2012 at 08:07 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    03-20-2011
    Location
    UK
    MS-Off Ver
    Excel 2007/10/16
    Posts
    840

    Re: Date change & TBA when match cancelled. Try Match and index didn't work

    I have try Match and Index it come up #REF! I done this =INDEX(E$48:Z$48,MATCH(D11,$D$48:$D$58,0),MATCH(E5,$E46:$Z$46,0),IF(OR(Setup!$C$18="",Setup!$C$20=""),"",IF(E$5="","",IF(INDEX(INDIRECT("'"&Setup!$N$5&"'!$e$23:$ab$59"),Setup!$M8,E$5)=0,"",INDEX(INDIRECT("'"&Setup!$N$5&"'!$e$23:$ab$59"),Setup!$M8,E$5))))).

    Anyone got any idea?

  3. #3
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Date change & TBA when match cancelled. Try Match and index didn't work

    i cant see what you are doing but
    but this =INDEX(E$48:Z$48,MATCH(D11,$D$48:$D$58,0),MATCH(E5,$E46:$Z$46,0),IF(OR(Setup!$C$18="",Setup!$C$20="" ),"",IF(E$5="","",IF(INDEX(INDIRECT("'"&Setup!$N$5&"'!$e$23:$ab$59"),Setup!$M8,E$5)=0,"",INDEX(INDIR ECT("'"&Setup!$N$5&"'!$e$23:$ab$59"),Setup!$M8,E$5))))).
    isnt right, the first bit only needs a final ) to finish it but you have some if statement
    =INDEX(E$48:Z$48,MATCH(D11,$D$48:$D$58,0),MATCH(E5,$E46:$Z$46,0)) the rest would never be used
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  4. #4
    Valued Forum Contributor
    Join Date
    03-20-2011
    Location
    UK
    MS-Off Ver
    Excel 2007/10/16
    Posts
    840

    Re: Date change & TBA when match cancelled. Try Match and index didn't work

    Thank you for reply.
    Maybe I not explain it clear. I agreed with this as it doe work =INDEX(E$48:Z$48,MATCH(D11,$D$48:$D$58,0),MATCH(E5,$E46:$Z$46,0). But no date for rest week 1 to 22.

    That why need put in IF(OR(Setup!$C$18="",Setup!$C$20="" ),"",IF(E$5="","",IF(INDEX(INDIRECT("'"&Setup!$N$5&"'!$e$23:$ab$59"),Setup!$M8,E$5)=0,"",INDEX(INDIRECT("'"&Setup!$N$5&"'!$e$23:$ab$59"),Setup!$M8,E$5))))). In order get date each week.

    If you look in example2 file in setup Look at Cell C18 date:20 October 2011 & C20 Thursday make IF(OR(Setup!$C$18="",Setup!$C$20="" ) Then in Chart cell E$5:Z$5 is number from 1 to 22 week make IF(E$5="","", If number not in between 18 to 22 become blank.

    Next come IF(INDEX(INDIRECT("'"&Setup!$N$5&"'!$e$23:$ab$59"),Setup!$M8,E$5)=0,"",INDEX(INDIRECT("'"&Setup!$N$5&"'!$e$23:$ab$59"),Setup!$M8,E$5))))).

    ("'"&Setup!$N$5&"'!$e$23:$ab$59"), Setup cell N5 is ch 11 or 12 sheet which is in hide. If you click on unhide you see it. last is Setup!$M8,E$5) Setup cell m5 is number from 1 to 552 pick up from ch 11 or 12 sheet every row or column.

    In Chart sheet you see all date done in =IF(OR(Setup!$C$18="",Setup!$C$20="" ),"",IF(E$5="","",IF(INDEX(INDIRECT("'"&Setup!$N$5&"'!$e$23:$ab$59"),Setup!$M8,E$5)=0,"",INDEX(INDIRECT("'"&Setup!$N$5&"'!$e$23:$ab$59"),Setup!$M8,E$5)))))

    But there a problem if team cancelled match. I put TBA or new date in Chart sheet cell E47:Z58 It doe not change where I highlight in orange cell E6:Z41

    I have try lot different code. Nothing work. I know it there somewhere I not got it right. I hope someone know. Hope this clear what I said. Any idea.
    Last edited by micope21; 01-06-2012 at 08:08 PM.

  5. #5
    Valued Forum Contributor
    Join Date
    03-20-2011
    Location
    UK
    MS-Off Ver
    Excel 2007/10/16
    Posts
    840

    Re: Date change & TBA when match cancelled. Try Match and index didn't work

    Anyone got any idea?

  6. #6
    Valued Forum Contributor
    Join Date
    03-20-2011
    Location
    UK
    MS-Off Ver
    Excel 2007/10/16
    Posts
    840

    Re: Date change & TBA when match cancelled. Try Match and index didn't work

    Thank you Haseeb A solved

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1