Combining IF, OR and 3d references
 

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:


--
duncan79
------------------------------------------------------------------------
duncan79's Profile: http://www.excelforum.com/member.php...o&userid=30833
View this thread: http://www.excelforum.com/showthread...hreadid=540255

Combining IF, OR and 3d references
 

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.

"duncan79" wrote:


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:


--
duncan79
------------------------------------------------------------------------
duncan79's Profile: http://www.excelforum.com/member.php...o&userid=30833
View this thread: http://www.excelforum.com/showthread...hreadid=540255

Combining IF, OR and 3d references
 

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


--
duncan79
------------------------------------------------------------------------
duncan79's Profile: http://www.excelforum.com/member.php...o&userid=30833
View this thread: http://www.excelforum.com/showthread...hreadid=540255

Combining IF, OR and 3d references
 

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.

"duncan79" wrote:


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


--
duncan79
------------------------------------------------------------------------
duncan79's Profile: http://www.excelforum.com/member.php...o&userid=30833
View this thread: http://www.excelforum.com/showthread...hreadid=540255

Combining IF, OR and 3d references
 

Thanks thats what i needed thanks

...

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

with this formula

=IF(ISNA(MATCH(C7,Lists!B1:Lists!B4,0)),"Animal",I F(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 others
it displays "Animal" so that seems backwards and "Vegetable" doesnt
appear at all

I am using absolute references will this make a difference??


--
duncan79
------------------------------------------------------------------------
duncan79's Profile: http://www.excelforum.com/member.php...o&userid=30833
View this thread: http://www.excelforum.com/showthread...hreadid=540255

Combining IF, OR and 3d references
 

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.

"duncan79" wrote:


Thanks thats what i needed thanks

...

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

with this formula

=IF(ISNA(MATCH(C7,Lists!B1:Lists!B4,0)),"Animal",I F(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 others
it displays "Animal" so that seems backwards and "Vegetable" doesnt
appear at all

I am using absolute references will this make a difference??


--
duncan79
------------------------------------------------------------------------
duncan79's Profile: http://www.excelforum.com/member.php...o&userid=30833
View this thread: http://www.excelforum.com/showthread...hreadid=540255

Combining IF, OR and 3d references
 

Thanks thats it sorted. Thankyou


--
duncan79
------------------------------------------------------------------------
duncan79's Profile: http://www.excelforum.com/member.php...o&userid=30833
View this thread: http://www.excelforum.com/showthread...hreadid=540255