software

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(quot;Squirelquot;, quot;Ratquot;))), quot;Animalquot;, quot;Blankquot;)

=IF(OR(C3=(OR(quot;!ListsB1quot;, quot;!ListsB2quot;))), quot;!ListsA1quot;, quot;Blankquot;)

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--
duncan79
------------------------------------------------------------------------
duncan79's Profile: www.excelforum.com/member.php...oamp;userid=30833
View this thread: www.excelforum.com/showthread...hreadid=540255It 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=quot;Squirelquot;,C3 = quot;Ratquot;), quot;Animalquot;, quot;Blankquot;)

Hope this helps,
Miguel.

quot;duncan79quot; wrote:

gt;
gt; I want to one validated entry from a list in a cell to auto fill another
gt; from a seperate list of possibilities. my lists are 3d referenced
gt;
gt; say i have as a dropdown option
gt;
gt; Squirel
gt; Rat
gt; Carrot
gt; Cucumber
gt;
gt; on !Lists sheet B column
gt;
gt; and
gt;
gt; Animal
gt; Vegetable
gt;
gt; on !Lists sheet A column
gt;
gt; I have tried several things and they just return #Value regardless of
gt; whether i use a reference or the text itself eg:
gt;
gt; =IF(OR(C3=(OR(quot;Squirelquot;, quot;Ratquot;))), quot;Animalquot;, quot;Blankquot;)
gt;
gt; =IF(OR(C3=(OR(quot;!ListsB1quot;, quot;!ListsB2quot;))), quot;!ListsA1quot;, quot;Blankquot;)
gt;
gt; I havent even tried entering the second 'vegetable' condition into the
gt; formula yet and i have tried several arrangments of the formula. (with
gt; or without the second OR)
gt;
gt; Can anyone help??
gt; If anyone can help with the second condition too that would be great.
gt;
gt; Thanks
gt;
gt; duncan
gt;
gt;
gt; --
gt; duncan79
gt; ------------------------------------------------------------------------
gt; duncan79's Profile: www.excelforum.com/member.php...oamp;userid=30833
gt; View this thread: www.excelforum.com/showthread...hreadid=540255
gt;
gt;


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), quot;Animalquot;, quot;notquot;)

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: www.excelforum.com/member.php...oamp;userid=30833
View this thread: www.excelforum.com/showthread...hreadid=540255Well, you may use a different formula. Instead of OR, check this one:
=IF(ISNA(MATCH(C4,Lists!B1:B2,0)), quot;Animalquot;, quot;notquot;)
You can extend the list as much as you want.

Miguel.

quot;duncan79quot; wrote:

gt;
gt; Brilliant that works thanks
gt;
gt; is there any way of saying it in shorter hand by referencing ranges as
gt; in
gt;
gt; =IF(OR(C4 = Lists!B1:B2), quot;Animalquot;, quot;notquot;)
gt;
gt; or do i have to enter each one by one??? i am working with a fair
gt; amount of conitions so it would be to long winded to be entirely
gt; practical
gt;
gt;
gt; --
gt; duncan79
gt; ------------------------------------------------------------------------
gt; duncan79's Profile: www.excelforum.com/member.php...oamp;userid=30833
gt; View this thread: www.excelforum.com/showthread...hreadid=540255
gt;
gt;


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)),quot;Animalquot;,I F(ISNA(MATCH(C7,Lists!B5:B25,0)),quot;Mineralquot;,IF(ISNA (MATCH(C7,Lists!B26:B54,0)),quot;Vegetablequot;,quot;blankquot;))If the entry is between B1:B4 it diplays quot;Mineralquot; and for all others
it displays quot;Animalquot; so that seems backwards and quot;Vegetablequot; doesnt
appear at all

I am using absolute references will this make a difference??--
duncan79
------------------------------------------------------------------------
duncan79's Profile: www.excelforum.com/member.php...oamp;userid=30833
View this thread: www.excelforum.com/showthread...hreadid=540255Ups, 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 )),quot;blankquot;,quot;Vegetablequot;),quot;Mineralquot;),quot;Animalquot;)

Sorry for the confusion,
Miguel.

quot;duncan79quot; wrote:

gt;
gt; Thanks thats what i needed thanks
gt;
gt; ...
gt;
gt; only i've run into a strange problem (i probably just dont know how
gt; that function operates)
gt;
gt; with this formula
gt;
gt; =IF(ISNA(MATCH(C7,Lists!B1:Lists!B4,0)),quot;Animalquot;,I F(ISNA(MATCH(C7,Lists!B5:B25,0)),quot;Mineralquot;,IF(ISNA (MATCH(C7,Lists!B26:B54,0)),quot;Vegetablequot;,quot;blankquot;))
gt;
gt;
gt; If the entry is between B1:B4 it diplays quot;Mineralquot; and for all others
gt; it displays quot;Animalquot; so that seems backwards and quot;Vegetablequot; doesnt
gt; appear at all
gt;
gt; I am using absolute references will this make a difference??
gt;
gt;
gt; --
gt; duncan79
gt; ------------------------------------------------------------------------
gt; duncan79's Profile: www.excelforum.com/member.php...oamp;userid=30833
gt; View this thread: www.excelforum.com/showthread...hreadid=540255
gt;
gt;


Thanks thats it sorted. Thankyou--
duncan79
------------------------------------------------------------------------
duncan79's Profile: www.excelforum.com/member.php...oamp;userid=30833
View this thread: www.excelforum.com/showthread...hreadid=540255