Data Validation

J

Jo HAN

I'm trying to do a Comparrison of products of differant Companies.
My Excel Data is like this called lookup:
Company Product Item Description Character1 Character2
Character3 etc.
The moment I have the Item I can link the Rest like Description, Character1
etc via vlookup because it is unique.
Under a product one company can have more than one Item. I started with Data
Validation because this gives me an easy drop down list. For products. Then
I created one for Companies. Now I am stuck because I hoped to do a lookup
with a combo of Product and Company to give me a dropdown list of just the
items that exist against this combo but I can not get it right! The formula
that I used is =Indirect(Vlookup (of the Product and Company cells) where I
created the Company and Products as named lists. This is suppose to now
lookup my Data in the lookup database.

Can some one please help. The alternative would be VB to maybe replace the
named list with Items out of the database where the combo of Company and
Product is valid?

Tx
 
D

Debra Dalgleish

If cell A2 contains a company name and cell B2 contains a product name,
the data validation in cell C2 should be --

Allow: List
Source: =INDIRECT($A2 & "_" & $B2)

where there is a single column range named CompanyName_ProductName
 
J

Jo HAN

If I understand this right I then need to create 100's of Named ranges where
A2 and B2 combo exist?
 
J

Jo HAN

If I understand this right I then need to create 100's of Named ranges where
A2 and B2 combo exist?
 
D

Debra Dalgleish

If you want a dropdown with items unique to a company and product, you'd
have to create a named range for each combination.

But it's not clear to me what you're trying to do with the data. Perhaps
you could use a pivot table to compare the products. There are
instructions and links for pivot tables on Jon Peltier's site:

http://peltiertech.com/Excel/Pivots/pivotstart.htm
 
Top