Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Checking a cell against a named range
Hello Guys and Girls,
A have a issue with named ranges. I have a named range "TestRange", with the values "OK", "NOK". I'm trying to test a cell against this range, so I am using something like this: =IF(B8=TestRange;0;1) But checking a cell against all values within the named range doesn't seem to work as i expected... is there another way to implement this behaviour? Or am I doing this completely wrong? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Checking a cell against a named range
=--ISNUMBER(MATCH(B8;TestRange;0))
should work -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Memento" wrote in message ... Hello Guys and Girls, A have a issue with named ranges. I have a named range "TestRange", with the values "OK", "NOK". I'm trying to test a cell against this range, so I am using something like this: =IF(B8=TestRange;0;1) But checking a cell against all values within the named range doesn't seem to work as i expected... is there another way to implement this behaviour? Or am I doing this completely wrong? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Checking a cell against a named range
"Memento" skrev i en meddelelse
... Hello Guys and Girls, A have a issue with named ranges. I have a named range "TestRange", with the values "OK", "NOK". I'm trying to test a cell against this range, so I am using something like this: =IF(B8=TestRange;0;1) But checking a cell against all values within the named range doesn't seem to work as i expected... is there another way to implement this behaviour? Or am I doing this completely wrong? Hello One way, for a 1- or 2-dimensional TestRange: For result True or False =SUMPRODUCT((B8=TestRange)+0)<1 For result 0 or 1 =(SUMPRODUCT((B8=TestRange)+0)<1)+0 -- Best regards Leo Heuser Followup to newsgroup only please. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Checking a cell against a named range
=--(COUNTIF(TestRange,B8)0)
"Memento" wrote: Hello Guys and Girls, A have a issue with named ranges. I have a named range "TestRange", with the values "OK", "NOK". I'm trying to test a cell against this range, so I am using something like this: =IF(B8=TestRange;0;1) But checking a cell against all values within the named range doesn't seem to work as i expected... is there another way to implement this behaviour? Or am I doing this completely wrong? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Checking a cell against a named range
OK, so the past few days i've been trying all suggestions from you guys...
unfortunately it doesn't work as it should... There are two things that keep cumming up... First of all only the "Value if untrue" keeps on popping up, although the checked cell value is within the named range, so in this case the "value if true" should show up... When i edit the formula, I can actually see that Excel 2007 recognizes the formula, because he surrounds the range with a colored box. Also, all formulas mentioned here keep on giving me some kind of error... Let me again explain what i need: I have a list with these items: "vb", "FD", "VFD", "VA", "VB", "JV", "ELF", etc..." If we want to check a specific cell against one of the above values, we are using this form of formula: =(IF(OR(K64="TK";K64="VA";K64="VB";K64="VC";K64="E LF";K64="JV"... etc)) I thought I could use "named ranges", to shorten the formula significantly. However this doesn't seem to work as it should, or I am doing something completely wrong. A few formulas i used to do some testing: I have a few items put up in a named range named "TestRange", and i have tried to use the following formulas aside from the ones in the replies, without success: =IF(ISTEXT(EQUALS(D2;TestRange));OK;NOK) =ALS(EQUALS(D8;TestRange);"OK";"NOK") "Teethless mama" wrote: =--(COUNTIF(TestRange,B8)0) "Memento" wrote: Hello Guys and Girls, A have a issue with named ranges. I have a named range "TestRange", with the values "OK", "NOK". I'm trying to test a cell against this range, so I am using something like this: =IF(B8=TestRange;0;1) But checking a cell against all values within the named range doesn't seem to work as i expected... is there another way to implement this behaviour? Or am I doing this completely wrong? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Checking a cell against a named range
Any of the options provided should work. Which language are you working in?
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Memento" wrote in message ... OK, so the past few days i've been trying all suggestions from you guys... unfortunately it doesn't work as it should... There are two things that keep cumming up... First of all only the "Value if untrue" keeps on popping up, although the checked cell value is within the named range, so in this case the "value if true" should show up... When i edit the formula, I can actually see that Excel 2007 recognizes the formula, because he surrounds the range with a colored box. Also, all formulas mentioned here keep on giving me some kind of error... Let me again explain what i need: I have a list with these items: "vb", "FD", "VFD", "VA", "VB", "JV", "ELF", etc..." If we want to check a specific cell against one of the above values, we are using this form of formula: =(IF(OR(K64="TK";K64="VA";K64="VB";K64="VC";K64="E LF";K64="JV"... etc)) I thought I could use "named ranges", to shorten the formula significantly. However this doesn't seem to work as it should, or I am doing something completely wrong. A few formulas i used to do some testing: I have a few items put up in a named range named "TestRange", and i have tried to use the following formulas aside from the ones in the replies, without success: =IF(ISTEXT(EQUALS(D2;TestRange));OK;NOK) =ALS(EQUALS(D8;TestRange);"OK";"NOK") "Teethless mama" wrote: =--(COUNTIF(TestRange,B8)0) "Memento" wrote: Hello Guys and Girls, A have a issue with named ranges. I have a named range "TestRange", with the values "OK", "NOK". I'm trying to test a cell against this range, so I am using something like this: =IF(B8=TestRange;0;1) But checking a cell against all values within the named range doesn't seem to work as i expected... is there another way to implement this behaviour? Or am I doing this completely wrong? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Checking a cell against a named range
Thanks Bob,
I am using the Dutch version, as I reside in Belgium :-). Although I think I'm able to extract the dutch keywords: IF means ALS in Dutch.. and so on, but it seems i am able to use some in dutch, some in english. If i use: =ISNUMBER(MATCH(B8;TestRange;0)) - this gives me: name not valid This one causes the problem with "MATCH" I guess... i tried "GELIJK" in dutch, but that one doesn't seem to match up... :-( I also tried: =ALS(GELIJK(C3;TestRange);"OK";"NOK") This one gives me only OK when the value matches on the same row: so A3 is BOB and C3 is also BOB, then it gives me OK. If i change it into something else, it becomes NOK. A1:A5 = named range "TestRange": A B C D 1 Memento Bob =IF(EQUALS(C1;TestRange);"OK";"NOK") =OK 2 Bob Marc =IF(EQUALS(C1;TestRange);"OK";"NOK") =NOK 3 Leo TM 4 TM 5 John So... Cel D1 should give me the value "OK" if Bob falls within the range "TestRange". Somehow it just gives me the error "name not valid". However, if i click on the name "TestRange" in the formula bar I see it recognizes the name because Excel surrounds it with a colored box when i select it... If I can manage to get this working with your help guys, that would be amazing. I also noted there are alot of questions relating to this kind of issue, so I'm guessing that this kind of "functionality" is quite tricky. Anyway. already thanks for all the help and advise, but i'm not going to give up on this one! BTW: I am using the Dutch version of Excel 2007. With regards, Memento |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Checking a cell against a named range
Okay, I've got a result here Bob:
I've used: =ALS(VERGELIJKEN(C3;TestRange;0);"OK";"NOK") I guess this would: =IF(COMPARE(C3;TestRange;0);"OK";"NOK") One slight problem though: When i use another value, one that is not in the list, it gives me: #N/B or unknown value. Is there a way to get rid of this one.. Thanks for all the advice already guys |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Checking a cell against a named range
=ALS(ISNB(VERGELIJKEN(C3;TestRange;0)"0");"OK";"NO K")
In English: =IF(ISNB(COMPARE(C3;TestRange;0)"0");"OK";"NOK") Doesn't work also, the placing of the ISNB seems to be wrong in this one. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
get data from a cell in each named range | Excel Worksheet Functions | |||
I want to use a cell Value as a named Range in a Formula | Excel Worksheet Functions | |||
inserting a named range into new cells based on a named cell | Excel Discussion (Misc queries) | |||
named cell range | Excel Worksheet Functions | |||
Getting a named range from a cell value | Excel Worksheet Functions |