Results 1 to 10 of 10
  1. #1
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts

    unexpected Match result (Excel2000)

    I have an Excel file which uses a formula to match a specified value in one of 7 external CSV format files (A003LOG.CSV, B003LOG.CSV, C003LOG.CSV, .. , G003LOG.CSV)
    The formula returns the row number of the specified item, or, if not found, the next available row for posting a new entry.
    If I open the sample files in the attached zipfile and then specify a User ID of either A003, B003, D003, E003, F003 or G003 in the Excel spreadsheet, I get the expected result. For some obscure reason, if I specify a User ID of C003 I get #REF instead of an expected #N/A for an unmatched value.

    Can anyone replicate this, and if so, explain what is going on??????????

    All of the CSV files are the same apart from the filenames.

    Any thoughts much appreciated!!!

    zeddy
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: unexpected Match result (Excel2000)

    You would also get a #REF error if the file was named R003.CSV. The "C003" and "R003" in such file names are erroneously interpreted by Excel as R1C1 style refrerences. Not much you can do about it, I suspect, except avoiding file names like that.

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts

    Re: unexpected Match result (Excel2000)

    Many thanks for that insight!!!!

    slap my head and call me charlie.

    zeddy

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: unexpected Match result (Excel2000)

    Zeddy,

    Really simple solution:
    change the formula in D11 to ="'"&userID&"LOG.CSV'"

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts

    Re: unexpected Match result (Excel2000)

    Thanks for that - you are correct.
    Still puzzled me though why Excel interpreted C003LOG.CSV as R1C1 notation!

    zeddy

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: unexpected Match result (Excel2000)

    Apparently that is the way Excel parses strings - if a string begins with a "C" or "R" followed by a number, Excel assumes that it is a R1C1-style reference. It's also language dependent - in Dutch the problem occurs for names beginning with "K" (kolom) or "R" (rij) followed by a number.

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts

    Re: unexpected Match result (Excel2000)

    Thanks Hans!

    So if I'd used filenames like LOGC003.csv it would've been OK !
    How do you find out this stuff!
    I'm very impressed and I'll certainly watch out if I ever use the Dutch version!!!

    High regards

    zeddy

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: unexpected Match result (Excel2000)

    Hi Zeddy,

    I've known about this one for so long that I can't remember how I found out - probably by <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15> repeatedly... It has been mentioned in this forum before - see for instance <post#=69437>post 69437</post#>, but of course, if you have no idea what causes the problem, you won't look for something like that.

  9. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts

    Re: unexpected Match result (Excel2000)

    Hi Hans,

    Thanks for the thread, I looked at all those and might have guessed that Legare had it sussed.
    As you say, if you don't now what the problem is - it's hard to search.

    Thank goodeness we have the WOPR lounge with all you experts to assist!

    regards

    zeddy

  10. #10
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: unexpected Match result (Excel2000)

    I have also seen similar behaviour with the the DGET function where the search criteria was 'A-1'.

    I was going to post the occurence as an oddity on the board, bt was distracted by work pressures.

    Isn't it good to know that Microsoft is uniformly inconsistent?

Posting Permissions

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