Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    #Value ????? (xls 97)

    Hi there i have attached a file i am working on. I am using columns K:O to run If statements that check the titles of my tables on that page and return "ok" if it finds that title in the list. As you can see my division test works fine and returns the appropriate True/False values. Howver, when i run the same If statements for the countries I get #Value instead of a True/False response. I have checked the formatting and i am not including any numbers. Can anyone tell what is wrong.

    The text in the countries and division columns come from another xls file and I am runnign a crosscheck to ensure that all countries and divisions are included. Thanks in advance.
    Attached Files Attached Files

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: #Value ????? (xls 97)

    scrappe7

    When you use something like <font color=red> O2=D1824 </font color=red> that means you are comparing a single cell with a range of cells, and you need to <font color=red> Array-Enter </font color=red> the formula.

    Write the formula and when you are done hold the <font color=blue> CTRL+Shift </font color=blue> and Hit Enter. The formula will be enclosed in <font color=red> { and } </font color=red> .

    HTH

    Wassim
    PS Hi Bob (Private joke if Bob U. is here today)
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: #Value ????? (xls 97)

    I tried that but the array does not work. It returns the false valuse always. It does not recognize to search fro the string value in the other cells. How can i make it smart???? For example,

    if a1 = cat

    c1 = turtle
    c2 = fox
    c3 = dog
    c4 =horse
    c = cat


    The statement {=(if {a1=(C:C), "ok", "error")} should return "ok" because cat is found in the C column. However, my statements are always reurning the false. IS this because the array uses an "and" and not an "or" statement?? WHat do you think?

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: #Value ????? (xls 97)

    For the WB you attached, enter this in to cell L2 and copy it down through row 11 (NOT as an array formula).

    [Edited:]

    <font color=red>=IF(ISNA(MATCH(M2,$A$2:$A$11)),M2,"OK")</font color=red>

    and let us know if that works for you. (I'm not 100% sure on what you are testing for.)

    However, in the WB you attached, the column N formulas will never work as you appear to comparing country name in column O with a number in Column D ... ?
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: #Value ????? (xls 97)

    scrappe7

    As John, I also do not understand exactly what you are trying to do. So I am going to use your formula with some changes.

    <font color=red> =IF(O2<>$D$18:$D$24,"OK",O2) </font color=red>

    Two things I changed, 1) I changed the = to <> and 2) I changed to absolute refs.

    I think that you are trying to see if a value is in a list, and there are many ways to do that, some good and some not as good, but not understanding what you are trying to do, makes it hard to figure which approach will be the best one.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  6. #6
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: #Value ????? (xls 97)

    Hi John,

    What I am doing is linking columns M and O from another worksheet that produces a list of all the countries and divisions on our database. I then want to check these lists to the tables that are on the same page. So that if a country/division appears on our database but does not have any record on the table the user will be flagged. All i want is to check if that division/country is also found in one of the charts.

    Even when I only select 2 cells to look in in column D, i get the same error. Both contain text not numbers???

    What you suggested always gives back "OK" as long as any text is found. It is snot checking exact spelling of the words, just for words. Get it? i need to find that specific text(cell value) in a range of other cells. Thanks.

  7. #7
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: #Value ????? (xls 97)

    I need to check that a value in one cell exist in a range of other cells. The colored columns peach and blue get their info from links on other sheets. I then want to check if that country/division was included in my table to the right. The table titles are all in column A now. If the value in the colored cell is not found somewhere in column A then I want what is missing to be visible in the cell.
    Attached Files Attached Files

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: #Value ????? (xls 97)

    I'm sorry, I'm not getting it.

    In looking at your Worksheet, I see that ranges D2:J7, E19:H24, and any text in columns M & O are links from other files. My proposed formula =IF(ISNA(MATCH(M2,$A$2:$A$11)),M2,"OK") ensures that an exact spelling match exists from any column M text to any column A text, returns "OK" if a match is found, and if no match is found returns whatever is in the formula cell Row in Column M.

    I can't see what you want to compare with Column O as there are no other text fields in the WS to match it.

    Can you explain more carefully exactly what comparison test you want?
    -John ... I float in liquid gardens
    UTC -7ąDS

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: #Value ????? (xls 97)

    VLOOKUP seems like a good method. If your list of countries is named "Countries", then if in L2 you use <pre> =IF(ISNA(VLOOKUP(M2,Countries,1,FALSE)),"Not Found","Ok")</pre>

    you should get what you want. You can adopt a similar approach for Division.

    Your workbook attached with above example.

    Andrew
    Attached Files Attached Files

  10. #10
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: #Value ????? (xls 97)

    Darn, just saw your list of Countries in D1924, was about to post an attachment and I see Andrew has beaten me to it. I had just figured out that =Match() was a bad choice for what you want.
    -John ... I float in liquid gardens
    UTC -7ąDS

  11. #11
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: #Value ????? (xls 97)

    It's ok John, if you feel you need more questions to beat Andrew I'm your guy. thanks for the help. Its working great now.

  12. #12
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: #Value ????? (xls 97)

    John --

    A more effiecient formula for the task would be:

    =COUNTIF(Countries,M2)>0

    where Countries names a range in a column (or in a row for that matters).

    By the way, MATCH is not a bad choice as you seem to think. I'd rather prefer it to VLOOKUP in this case (for esthetical reasons):

    So:

    =ISNUMBER(MATCH(M2,Countries,0)

    would do just as well.

    Note. Both formulas intendedly return a logical value, either TRUE or FALSE.
    Microsoft MVP - Excel

Posting Permissions

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