Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 6 of 6 FirstFirst ... 456
Results 51 to 57 of 57

Thread: Duplicates

  1. #51
    Board Regular zacemmel's Avatar
    Join Date
    Apr 2002
    Location
    Redmond, WA
    Posts
    636
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Chris,

    You have mail.

  2. #52
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Zac,

    your formula changes in cell B6



  3. #53
    Board Regular zacemmel's Avatar
    Join Date
    Apr 2002
    Location
    Redmond, WA
    Posts
    636
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    =IF(ISERROR(INDIRECT("a"&MIN(IF(MATCH($A$1:$A$13,$A$1:$A$13,0)>MATCH(B4,$A$1:$A$13,0),MATCH($A$1:$A$13,$A$1:$A$13,0),99999)))),"",INDIRECT("a"&MIN(IF(MATCH($A$1:$A$13,$A$1:$A$13,0)>MATCH(B4,$A$1:$A$13,0),MATCH($A$1:$A$13,$A$1:$A$13,0),99999))))

    This is the correct formula.

    [ This Message was edited by: zacemmel on 2002-05-06 10:38 ]

  4. #54
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-06 10:37, zacemmel wrote:
    =IF(ISERROR(INDIRECT("a"&MIN(IF(MATCH($A$1:$A$13,$A$1:$A$13,0)>MATCH(B4,$A$1:$A$13,0),MATCH($A$1:$A$13,$A$1:$A$13,0),99999)))),"",INDIRECT("a"&MIN(IF(MATCH($A$1:$A$13,$A$1:$A$13,0)>MATCH(B4,$A$1:$A$13,0),MATCH($A$1:$A$13,$A$1:$A$13,0),99999))))

    This is the correct formula.

    [ This Message was edited by: zacemmel on 2002-05-06 10:38 ]
    if I make cell A4 blank, I get nothing back except "mary" in B1


  5. #55
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    also Zac, it might have been slightly useful to have told us that this is an array-entered formula, entered with CNTRL SHIFT ENTER, as oppose to a regular formula


  6. #56
    Board Regular zacemmel's Avatar
    Join Date
    Apr 2002
    Location
    Redmond, WA
    Posts
    636
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The data has to be contiguous and adjacent. I thought it was inferred that it was an array formula. I guess not

  7. #57
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    okay, so *if* it's adjacent and contiguous and you don't mind editting the formula to fit your exact data range each time, remembering not to put it in the first row, I concur that it works.

    Gotta admire your perserverance !



Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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