Results 1 to 5 of 5

Youtube Excel Magic Trick 538 - Question

This is a discussion on Youtube Excel Magic Trick 538 - Question within the Excel Questions forums, part of the Question Forums category; http://www.youtube.com/watch?v=tqCEY5YMyqw I am trying to duplicate the concept on my spreadsheet. It is conceptually the same, the formula is slightly ...

  1. #1
    New Member
    Join Date
    Apr 2010
    Posts
    9

    Default Youtube Excel Magic Trick 538 - Question

    http://www.youtube.com/watch?v=tqCEY5YMyqw

    I am trying to duplicate the concept on my spreadsheet. It is conceptually the same, the formula is slightly different.

    Mine is as follows:
    {=IF(ROWS(A$5:A5)>$B$2,"",
    INDEX(US!A$5:A$72,
    SMALL(IF(US!$AU$5:$AU$72=Sheet1!$A$2,ROW(US!$AU$5:$AU$72)-ROW(US!$AU$5)+1),ROWS(A$5:A5))))}

    Resulting in a #VALUE! error. Can anybody advise me what should the formula me?

    Thank a million!
    Long
    Last edited by LongCR; Apr 9th, 2010 at 08:28 AM. Reason: key in text wrongly

  2. #2
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    13,947

    Default Re: Youtube Excel Magic Trick 538 - Question

    The formula needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER. If done correctly, Excel will automatically place curly braces {...} around the formula.
    Domenic Tamburino
    Microsoft MVP - Excel
    xl-central.com - "For Your Microsoft Excel Solutions"

  3. #3
    New Member
    Join Date
    Apr 2010
    Posts
    9

    Default Re: Youtube Excel Magic Trick 538 - Question

    Thanks for your reply.

    Yes I did that. Control + Shift + Enter. It still has the error.

    Can anybody help? And do a comparison with the formula in the Youtube? Thanks.

    Best wishes,
    Long

  4. #4
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    13,947

    Default Re: Youtube Excel Magic Trick 538 - Question

    Do any of the cells in the data contain the error #VALUE! ?
    Domenic Tamburino
    Microsoft MVP - Excel
    xl-central.com - "For Your Microsoft Excel Solutions"

  5. #5
    New Member
    Join Date
    Apr 2010
    Posts
    9

    Default Re: Youtube Excel Magic Trick 538 - Question

    Hi Dom,

    You are sooo right. Thank you very much for this!!!

    I spend the half my day trying to solve this!!



    With metta,
    Long

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
  •  


DMCA.com