software

I have a question reguarding a data validation formula:

Currenty I have a formula in cell b14 :
=INDIRECT(VLOOKUP(A14,TRCLOOKUP,2,0)).

What I want to add is an IF statement which will display a different
list (FOTRC) if the value in cell A14 is not in the quot;TRCLOOKUPquot; named
range.

Any sugestions?--
LewR
------------------------------------------------------------------------
LewR's Profile: www.excelforum.com/member.php...oamp;userid=32420
View this thread: www.excelforum.com/showthread...hreadid=525102Hi!

Try this:

=INDIRECT(VLOOKUP(A14,INDIRECT(IF(COUNTIF(INDEX(TR CLOOKUP,,1),A14),quot;TRCLOOKUPquot;,quot;FOTRCquot;)),2,0))

Biff

quot;LewRquot; gt; wrote in message
...
gt;
gt; I have a question reguarding a data validation formula:
gt;
gt; Currenty I have a formula in cell b14 :
gt; =INDIRECT(VLOOKUP(A14,TRCLOOKUP,2,0)).
gt;
gt; What I want to add is an IF statement which will display a different
gt; list (FOTRC) if the value in cell A14 is not in the quot;TRCLOOKUPquot; named
gt; range.
gt;
gt; Any sugestions?
gt;
gt;
gt; --
gt; LewR
gt; ------------------------------------------------------------------------
gt; LewR's Profile:
gt; www.excelforum.com/member.php...oamp;userid=32420
gt; View this thread: www.excelforum.com/showthread...hreadid=525102
gt;

Not sure I understand your question.

If TRCLOOKUP and FOTRC are two alternate names ranges of similar
structure and for similar (alternate) purposes, then try:

Formula in cell b14 :
=if(iserror(VLOOKUP(A14,TRCLOOKUP,2,0)),INDIRECT(V LOOKUP(A14,FOTRC,2,0)),INDIRECT(VLOOKUP(A14,TRCLOO KUP,2,0)))

Good luck,

P.S. If my assumptions are correct, you might need to consider a
similar if(iserror(... condition for the FOTRC vlookup.

Tip:
Putting the vlookups for TRCLOOKUP and FOTRC in separate cells (e.g.
b14 and c14 with the final formula instead in d14) will simplify the
formulae and improve calculation speed.--
John James
------------------------------------------------------------------------
John James's Profile: www.excelforum.com/member.php...oamp;userid=32690
View this thread: www.excelforum.com/showthread...hreadid=525102I remember answering a post from this OP a couple of weeks ago?

The lookup tables contain the names of named ranges to be used as the source
for a drop down list(s).

Biff

quot;John Jamesquot; gt; wrote
in message ...
gt;
gt; Not sure I understand your question.
gt;
gt; If TRCLOOKUP and FOTRC are two alternate names ranges of similar
gt; structure and for similar (alternate) purposes, then try:
gt;
gt; Formula in cell b14 :
gt; =if(iserror(VLOOKUP(A14,TRCLOOKUP,2,0)),INDIRECT(V LOOKUP(A14,FOTRC,2,0)),INDIRECT(VLOOKUP(A14,TRCLOO KUP,2,0)))
gt;
gt; Good luck,
gt;
gt; P.S. If my assumptions are correct, you might need to consider a
gt; similar if(iserror(... condition for the FOTRC vlookup.
gt;
gt; Tip:
gt; Putting the vlookups for TRCLOOKUP and FOTRC in separate cells (e.g.
gt; b14 and c14 with the final formula instead in d14) will simplify the
gt; formulae and improve calculation speed.
gt;
gt;
gt; --
gt; John James
gt; ------------------------------------------------------------------------
gt; John James's Profile:
gt; www.excelforum.com/member.php...oamp;userid=32690
gt; View this thread: www.excelforum.com/showthread...hreadid=525102
gt;

Biff is correct, he did help me before.

I tried his response but it disd not do what I need it to do. Let me
restate the problem to clarify (or muddy) the air.

In cell A14 I have a Data Validation list that is controled by the
statement:=JobDescription. Job Description is a named range with a
blank row included in the list so that the user may enter a value if
the values that are named in the list are not what the user needs.
Cell B14 is another Data Validation list with the following formula:
=INDIRECT(VLOOKUP(A14,TRCLookup,2,0)).

What I want to add to B14 is a way to have the named Range quot;FOTRCquot; to
be used instead of quot;TRCLookupquot; if the blank row is chosen in cell a14.

Hope this makes sense.--
LewR
------------------------------------------------------------------------
LewR's Profile: www.excelforum.com/member.php...oamp;userid=32420
View this thread: www.excelforum.com/showthread...hreadid=525102Hi!

gt; What I want to add to B14 is a way to have the named Range quot;FOTRCquot; to
gt; be used instead of quot;TRCLookupquot; if the blank row is chosen in cell a14.

Ok, now it's getting confusing!

If A14 is blank then use FOTRC in the lookup?

=INDIRECT(VLOOKUP(A14,FOTRC,2,0))

Well, A14 is blank and unless you have a specially crafted quot;blankquot; selection
in the lookup table then there's nothing to lookup!

Need more info.

Biff

quot;LewRquot; gt; wrote in message
...
gt;
gt; Biff is correct, he did help me before.
gt;
gt; I tried his response but it disd not do what I need it to do. Let me
gt; restate the problem to clarify (or muddy) the air.
gt;
gt; In cell A14 I have a Data Validation list that is controled by the
gt; statement:=JobDescription. Job Description is a named range with a
gt; blank row included in the list so that the user may enter a value if
gt; the values that are named in the list are not what the user needs.
gt; Cell B14 is another Data Validation list with the following formula:
gt; =INDIRECT(VLOOKUP(A14,TRCLookup,2,0)).
gt;
gt; What I want to add to B14 is a way to have the named Range quot;FOTRCquot; to
gt; be used instead of quot;TRCLookupquot; if the blank row is chosen in cell a14.
gt;
gt; Hope this makes sense.
gt;
gt;
gt; --
gt; LewR
gt; ------------------------------------------------------------------------
gt; LewR's Profile:
gt; www.excelforum.com/member.php...oamp;userid=32420
gt; View this thread: www.excelforum.com/showthread...hreadid=525102
gt;

OK, I have solved part of the problem. in cell B14 I now have the Data
Validation Statement:
=IF(A14=quot;quot;,FOList,(INDIRECT(VLOOKUP(A14,TRCLookup, 2,0))))

quot;FOLookupquot; is a replacement for quot;FOTRCquot;.

This works as long as A14 is blank. How can I rewrite the formula in
B14 to produce the same results if the value in A14 is not listed in
the named range quot;TRCLookupquot;? The values in A14 will be text entries,
not numbers.--
LewR
------------------------------------------------------------------------
LewR's Profile: www.excelforum.com/member.php...oamp;userid=32420
View this thread: www.excelforum.com/showthread...hreadid=525102Try this:

=IF(A14=quot;quot;,FOList,IF(ISNA(VLOOKUP(A14,TRCLookup,2, 0)),FOList,INDIRECT(VLOOKUP(A14,TRCLookup,2,0))))

Biff

quot;LewRquot; gt; wrote in message
...
gt;
gt; OK, I have solved part of the problem. in cell B14 I now have the Data
gt; Validation Statement:
gt; =IF(A14=quot;quot;,FOList,(INDIRECT(VLOOKUP(A14,TRCLookup, 2,0))))
gt;
gt; quot;FOLookupquot; is a replacement for quot;FOTRCquot;.
gt;
gt; This works as long as A14 is blank. How can I rewrite the formula in
gt; B14 to produce the same results if the value in A14 is not listed in
gt; the named range quot;TRCLookupquot;? The values in A14 will be text entries,
gt; not numbers.
gt;
gt;
gt; --
gt; LewR
gt; ------------------------------------------------------------------------
gt; LewR's Profile:
gt; www.excelforum.com/member.php...oamp;userid=32420
gt; View this thread: www.excelforum.com/showthread...hreadid=525102
gt;

Thanks for the help, works like a dream--
LewR
------------------------------------------------------------------------
LewR's Profile: www.excelforum.com/member.php...oamp;userid=32420
View this thread: www.excelforum.com/showthread...hreadid=525102You're welcome. Glad we got it straightened out!Biff

quot;LewRquot; gt; wrote in message
...
gt;
gt; Thanks for the help, works like a dream
gt;
gt;
gt; --
gt; LewR
gt; ------------------------------------------------------------------------
gt; LewR's Profile:
gt; www.excelforum.com/member.php...oamp;userid=32420
gt; View this thread: www.excelforum.com/showthread...hreadid=525102
gt;