Help...............again
MZ Tools makes life easier for the Excel VBA coder
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Help...............again

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    Australia
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Ok, i have this formula, it takes data from external spreadsheets and records it in the main spreadsheet containing A5 (see below).

    =IF(ISNUMBER(MATCH(A5,week1.xls!$A$1:$A$10,0)),
    -- this part works, it returns true or false. Tested it.

    IF(VLOOKUP(A5,week1.xls!$A$1:$D$10,3,0)>VLOOKUP(A5,week1.xls!$A$1:$D$10,4,0),1,0),
    -- this part works, it returns 1 or 0 Iff first lookup greater than second lookup.

    IF(VLOOKUP(A5,week1.xls!$A$1:$D$10,3,0) -- this last part does not work, and for the cells it is meant to put 1 or 0 in, it puts #N/A.

    Why does this happen when we get true or false returning, and the last piece of code is exactly the same as the first piece of code, just with the > sign changed to <.

    Complete code:
    =IF(ISNUMBER(MATCH(A5,week1.xls!$A$1:$A$10,0)), IF(VLOOKUP(A5,week1.xls!$A$1:$D$10,3,0)>VLOOKUP(A5,week1.xls!$A$1:$D$10,4,0),1,0), IF(VLOOKUP(A5,week1.xls!$A$1:$D$10,3,0)
    All the ranges are correct also.

    Any help would be greatly appreciated. Thanx

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Posts
    202
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    try this - it worked for me. i think you had a missing bracket and no false statement (ie. "0")

    =IF(ISNUMBER(MATCH(A5,week1.xls!$A$1:$A$10,0)),IF(VLOOKUP(A5,week1.xls!$A$1:$D$10,3,0)>VLOOKUP(A5,week1.xls!$A$1:$D$10,4,0),1,0),IF(VLOOKUP(A5,week1.xls!$A$1:$D$10,3,0),0))

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Location
    Australia
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    It still didnt work, it still gives me #N/A values. I really don't get it.

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,767
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

      
    Go to that "external spreadsheet", locate A1:D10, activate an empty cell, type =, select A1:D10, hit F9, copy what you see after the =-sign, and paste it in the follow up post.

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