lists from other lists in excel

[Lew]

I want to make a drop down list from a named range (WO) when the other
conditions of the existing DIRECT statement are not true. The existing cell
reverence is :"DIRECT(VLOOKUP(A14,TRCLookup,2,0))" I want to add a condition
that will let me select from the list "WO" if the value in A14 is not in in
the "TRCLookup" . Any sugestions?

 

[Biff]

Hi!

What is "DIRECT"? Never heard of it. Must be a UDF?

Try this:

=IF(COUNTIF(INDEX(TRCLookup,,1),A14),VLOOKUP(A14,TRCLookup,2,0),VLOOKUP(A14,WO,2,0))

Biff

 

[Lew]

Oops, my mistake: The Data validation statement I am using
is:=INDIRECT(VLOOKUP(A14,TRCLOOKUP,2,0)). The IF Count you sugessted does not
work in my spreadsheet, I still recieve a blank drop down list if the value
in A14 is not in the TRCLookup list.

 

[Biff]

Ok, assumption:

The result of your lookup is a named range.

For data validation as the source:

=INDIRECT(IF(COUNTIF(INDEX(TRC,,1),A14),VLOOKUP(A14,TRC,2,0),VLOOKUP(A14,WO,2,0)))

Here's a sample file that demonstrates this:

http://s65.yousendit.com/d.aspx?id=07Q21XE78Y9WE16UD0KBV0P6T4

Biff

 

[Max]

.. Here's a sample file that demonstrates this:
http://s65.yousendit.com/d.aspx?id=07Q21XE78Y9WE16UD0KBV0P6T4

In the sample file's Sheet1,
think the letter "C" in E4 needs to be revised a little to "Cx",
to enable the DV in A1 to work when we select "3" from the DV in A14
(Think "C" itself as a named range is not allowed, so you used "Cx" <g>)

 

[Biff]

Yes, I missed that!

Biff

In the sample file's Sheet1,
think the letter "C" in E4 needs to be revised a little to "Cx",
to enable the DV in A1 to work when we select "3" from the DV in A14
(Think "C" itself as a named range is not allowed, so you used "Cx" <g>)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik