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 "TRCLOOKUP" named
range.

Any sugestions?


--
LewR
------------------------------------------------------------------------
LewR's Profile: http://www.excelforum.com/member.php...o&userid=32420
View this thread: http://www.excelforum.com/showthread...hreadid=525102

Hi!

Try this:

=INDIRECT(VLOOKUP(A14,INDIRECT(IF(COUNTIF(INDEX(TR CLOOKUP,,1),A14),"TRCLOOKUP","FOTRC")),2,0))

Biff

"LewR" wrote in message
...

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 "TRCLOOKUP" named
range.

Any sugestions?


--
LewR
------------------------------------------------------------------------
LewR's Profile:
http://www.excelforum.com/member.php...o&userid=32420
View this thread: http://www.excelforum.com/showthread...hreadid=525102

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: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=525102

I 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

"John James" wrote
in message ...

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:
http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=525102

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 "FOTRC" to
be used instead of "TRCLookup" if the blank row is chosen in cell a14.

Hope this makes sense.


--
LewR
------------------------------------------------------------------------
LewR's Profile: http://www.excelforum.com/member.php...o&userid=32420
View this thread: http://www.excelforum.com/showthread...hreadid=525102

Hi!

What I want to add to B14 is a way to have the named Range "FOTRC" to
be used instead of "TRCLookup" 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 "blank" selection
in the lookup table then there's nothing to lookup!

Need more info.

Biff

"LewR" wrote in message
...

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 "FOTRC" to
be used instead of "TRCLookup" if the blank row is chosen in cell a14.

Hope this makes sense.


--
LewR
------------------------------------------------------------------------
LewR's Profile:
http://www.excelforum.com/member.php...o&userid=32420
View this thread: http://www.excelforum.com/showthread...hreadid=525102

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

"FOLookup" is a replacement for "FOTRC".

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 "TRCLookup"? The values in A14 will be text entries,
not numbers.


--
LewR
------------------------------------------------------------------------
LewR's Profile: http://www.excelforum.com/member.php...o&userid=32420
View this thread: http://www.excelforum.com/showthread...hreadid=525102

Try this:

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

Biff

"LewR" wrote in message
...

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

"FOLookup" is a replacement for "FOTRC".

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 "TRCLookup"? The values in A14 will be text entries,
not numbers.


--
LewR
------------------------------------------------------------------------
LewR's Profile:
http://www.excelforum.com/member.php...o&userid=32420
View this thread: http://www.excelforum.com/showthread...hreadid=525102

Thanks for the help, works like a dream


--
LewR
------------------------------------------------------------------------
LewR's Profile: http://www.excelforum.com/member.php...o&userid=32420
View this thread: http://www.excelforum.com/showthread...hreadid=525102