#Value ????? (xls 97)

Topic

New Reply

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.

Reply | Quote

Replies

scrappe7

When you use something like O2=D18:D24 that means you are comparing a single cell with a range of cells, and you need to Array-Enter the formula.

Write the formula and when you are done hold the CTRL+Shift and Hit Enter. The formula will be enclosed in { and } .

HTH

Wassim
PS Hi Bob (Private joke if Bob U. is here today)

Reply | Quote

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?

Reply | Quote

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

[Edited:]

=IF(ISNA(MATCH(M2,$A$2:$A$11)),M2,”OK”)

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 … ?

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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.

=IF(O2$D$18:$D$24,”OK”,O2)

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

Reply | Quote

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.

Reply | Quote

VLOOKUP seems like a good method. If your list of countries is named “Countries”, then if in L2 you use

    =IF(ISNA(VLOOKUP(M2,Countries,1,FALSE)),"Not Found","Ok")

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

Your workbook attached with above example.

Andrew

Reply | Quote

Darn, just saw your list of Countries in D19:D24, 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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote