vlookup and validating data list

[Donna]

I use Excel 2007, but will need to share this workbook with 2003 users.

On worksheet 1, I want to pick from a list of possible options for info that
goes into column for Subs from the row that matches the Mfg on worksheet 2.

Not all Mfg may have a Sub option, therefore I may want to repeat the Mfg
name.
Both worksheets will have adds and deletes over the course of the year.

Worksheet 1

Parts Mfg Sub
123 Stars ??
234 Color ??
345 Cars ??
888 Color ??
777 Color ??
557 Cars ??
735 States ??

Worksheet 2
Mfg Sub
Stars Venus Mars Saturn Jupiter
Color Blue Black Red Green Yellow
Cars Ford GM Toyato
States

I tried the vlookup statement, but it only returns the value from the column
that I define in the formula in worksheet 1 C2

=VLOOKUP(B2,'Worksheet 2'!A$2:H1066,2)

Answer would be Venus, but I would like for it to be Jupiter.

Because worksheet 2 is so large, it would be hard to create a name range for
each row.

Any suggestions?
Thanks so much

 

[Jim]

I'm not sure I understand it all, but your vlookup will return Jupiter if you
change the vlookup from ...,2) to ...,5)

good luck...
Jim

 

[Donna]

True, but I don't always know the column B-H that contains the answer,
therefore, was hoping to create a list for that row to choose from after it
matches col A.

 

[T. Valko]

Let's see if I understand you...

If B2 = Stars then you want a drop down list in C2 with the selections:

Stars,Venus,Mars,Saturn,Jupiter

If B2 = Cars then you want a drop down list in C2 with the selections:

Cars,Ford,GM,Toyato

If B2 = States then you want a drop down list in C2 with the selections:

States

If that's the case...

With this data on Sheet2 in the range A2:F5 -

Stars,Venus,Mars,Saturn,Jupiter
Color,Blue,Black,Red,Green,Yellow
Cars,Ford,GM,Toyato
States

Create this named formula:

Insert>Name>Define
Name: List
Refers to:

=OFFSET(Sheet2!$A$2,MATCH($B$2,Sheet2!$A$2:$A$5,0)-1,,,COUNTA(OFFSET(Sheet2!$A$2,MATCH($B$2,Sheet2!$A$2:$A$5,0)-1,,,100)))

Then on Sheet1 select cell C2
Data>Validation
Allow: List
Source: =List

 

[Donna]

Thanks so much for the reply,. Your assessment is correct, but I've not be
able to get it to work. After last step doing the Data>Validation, I get an
error: "The Source currently evaluates to an error. Do you want to
continue? Yes No" When I select Yes, it has the drop down button, but there
is no list.

You made reference to the range of A2:F5 but in the formula it was A2:A5.
Is that correct?

 

[T. Valko]

Here's a small sample file that demonstrates this:

xdropdown.xls 14kb

http://cjoint.com/?cCtoxeKAzM

 

[Tim Brown]

I loved the file, very cool.

Can this also be done vertically rather than horizontally?

Tim.

 

[T. Valko]

Can this also be done vertically rather than horizontally?

Sure.

Assume this data on Sheet2 A2: D7 -

Stars,Color,Cars,States
Venus,Blue,Ford
Mars,Black,GM
Saturn,Red,Toyato
Jupiter,Green
Yellow

Insert>Name>Define
Name: List
Refers to:

=OFFSET(Sheet2!$A$2,,MATCH(Sheet1!$B$2,Sheet2!$2:$2,0)-1,COUNTA(OFFSET(Sheet2!$A$2,,MATCH(Sheet1!$B$2,Sheet2!$2:$2,0)-1,100)))

Data>Validation
Allow: List
Source: =List

 

[Tim Brown]

I was able to get the list to work, but the validation only works with the
first column. Any sugestions? I tried it in a small scale and it worked
fine. could there be something different with the workbooks?

=OFFSET(Tables!$B$1,,MATCH('Tracking
Form'!$F$1,Tables!$1:$1,0)-1,COUNTA(OFFSET(Tables!$B$1,,MATCH('Tracking
Form'!$F$1,Tables!$1:$1,0)-1,100)))

Tim

 

[T. Valko]

Not sure what you mean by:

the validation only works with the first column

 

[Tim Brown]

I was able to enter data that was not in the second column when that column
was picked. - I did some reading and found that I had blank spaces in the
second column. I unchecked the blank cells box and it works fine.

Thanks for your help

Tim

 

[T. Valko]

You're welcome. Thanks for the feedback!