Combining Data Validation with a LOOKUP table

[philipjsmith]

Hi,

My question is "Is it possible to link data validation to a LOOKUP table?".

Suppose column A is a list of 5 products & each product has been chosen from a list by data validation.
Suppose that in column B the colour that can be entered is one of say 3 possibilities which is different for each product. The colour COULD be shown on a seperate LOOKUP table.

Can data validation be used to eliminate a colour that is not permissible?

For example:

Product Red Blue Yellow Black Green
1 Y Y N N Y
2 Y N N Y Y
3 Y Y Y N N
4 Y Y N Y N
5 N N Y Y Y

Y = permissible, N =not permissible

Thanks for any advice.

[WoodyHays]

I base data validation on pivot tables frequently, though it will require some code to update it the page setting after a color is selected, ececuted by either a button or mouse event.

I can not think of a reason why it wouldn't work on a look up table either, though it needs to be on the same sheet which may pose a problem.

The structure of your sample table will only return a validated Y or N, you probably want the actual color name though.

Hope this helps some.

Woody Hays

[Chris Davison]

On 2002-05-21 12:04, philipjsmith wrote:
Hi,

My question is "Is it possible to link data validation to a LOOKUP table?".

Suppose column A is a list of 5 products & each product has been chosen from a list by data validation.
Suppose that in column B the colour that can be entered is one of say 3 possibilities which is different for each product. The colour COULD be shown on a seperate LOOKUP table.

Can data validation be used to eliminate a colour that is not permissible?

For example:

Product Red Blue Yellow Black Green
1 Y Y N N Y
2 Y N N Y Y
3 Y Y Y N N
4 Y Y N Y N
5 N N Y Y Y

Y = permissible, N =not permissible

Thanks for any advice.

Hi Phillip,

do you mean something along the lines that *if* product 4 was chosen from the dropdown, then the only 3 colours available in the dropdown next to it would be red, blue or black, with no others being shown ?

(if yes, then yes, it's pretty easy to set up with a few well-structured named ranges and an =INDIRECT in the data validation...)

post back and we can take it further

[Brian from Maui]

Philip.

Have a look at this thread if you answered YES to Chris's question

http://www.mrexcel.com/board/viewtop...c=7345&forum=2

Sorry for jumping in Chris