Combining IF, OR and 3d references

  • Thread starter Thread starter duncan79
  • Start date Start date
D

duncan79

I want to one validated entry from a list in a cell to auto fill another
from a seperate list of possibilities. my lists are 3d referenced

say i have as a dropdown option

Squirel
Rat
Carrot
Cucumber

on !Lists sheet B column

and

Animal
Vegetable

on !Lists sheet A column

I have tried several things and they just return #Value regardless of
whether i use a reference or the text itself eg:

=IF(OR(C3=(OR("Squirel", "Rat"))), "Animal", "Blank")

=IF(OR(C3=(OR("!ListsB1", "!ListsB2"))), "!ListsA1", "Blank")

I havent even tried entering the second 'vegetable' condition into the
formula yet and i have tried several arrangments of the formula. (with
or without the second OR)

Can anyone help??
If anyone can help with the second condition too that would be great.

Thanks

duncan:confused:
 
It looks like the error is on the OR sintaxis, it just checks TRUE/FALSE
conditions, so if you want to provide lists of values, you may do something
like this:
=IF(OR(C3="Squirel",C3 = "Rat"), "Animal", "Blank")

Hope this helps,
Miguel.
 
Brilliant that works thanks

is there any way of saying it in shorter hand by referencing ranges as
in

=IF(OR(C4 = Lists!B1:B2), "Animal", "not")

or do i have to enter each one by one??? i am working with a fair
amount of conitions so it would be to long winded to be entirely
practical
 
Well, you may use a different formula. Instead of OR, check this one:
=IF(ISNA(MATCH(C4,Lists!B1:B2,0)), "Animal", "not")
You can extend the list as much as you want.

Miguel.
 
Thanks thats what i needed thanks

...

only i've run into a strange problem (i probably just dont know ho
that function operates)

with this formula

=IF(ISNA(MATCH(C7,Lists!B1:Lists!B4,0)),"Animal",IF(ISNA(MATCH(C7,Lists!B5:B25,0)),"Mineral",IF(ISNA(MATCH(C7,Lists!B26:B54,0)),"Vegetable","blank"))


If the entry is between B1:B4 it diplays "Mineral" and for all other
it displays "Animal" so that seems backwards and "Vegetable" doesn
appear at all

I am using absolute references will this make a difference?
 
Ups, I think I have mixed the TRUE/FALSE actions on the formula. The formula
is TRUE when the value is NOT there, so the conditions should be swaped.
Something like this:
=IF(ISNA(MATCH(C7,Lists!B1:B4,0)),IF(ISNA(MATCH(C7,Lists!B5:B25,0)),IF(ISNA(MATCH(C7,Lists!B26:B54,0)),"blank","Vegetable"),"Mineral"),"Animal")

Sorry for the confusion,
Miguel.
 
Back
Top