Youtube Excel Magic Trick 538 - Question

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Youtube Excel Magic Trick 538 - Question

  1. #1
    New Member
    Join Date
    Apr 2010
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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 09:28 AM. Reason: key in text wrongly

  2. #2
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    17,769
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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.

  3. #3
    New Member
    Join Date
    Apr 2010
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    17,769
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Youtube Excel Magic Trick 538 - Question

    Do any of the cells in the data contain the error #VALUE! ?

  5. #5
    New Member
    Join Date
    Apr 2010
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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

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
  •  

 

 
DMCA.com