Using formula on a cell with data validation

**shawnsonline** · 08-09-2012, 02:19 PM

I have 3 columns. In column A there is the data. In column B and C I am using dependent data validation to categorize and subcategorize the data. It works well but my question is this. If I create lists on a different sheet that has all the data, with the appropriate category and subcategory set up for data that will be repetitive, what is the best way to do it so if a value on the list on the other sheet is found in column A then column B and C automatically change?

Thank you in advance

**ChemistB** · 08-09-2012, 02:48 PM

Confusing post.

Let's say in A, you're putting foreign or domestic cars
and in sheet 2, you a list for foreign cars makers and one for domestic car makers. You use these lists on your column B
Then you have lists for models for each domestic and each foreign which are used in Col C

So you input in A2, foreign. In B2, you get a dropdown list of foreign car makers and you choose Toyota. In col C, you get a dropdown list of Toyota models and you choose Camry. What do you want to happen if you change A2 to domestic? Do you simply want B and C to clear?

**shawnsonline** · 08-09-2012, 04:46 PM

Yes I do have a list for my categories in Column B and I do have a list for my subcategories in C. But for instance let's say in column A I have "tundra". Well after I set a category of auto and a subcategory of truck then what I would like is every time afterward it would be nice if when tundra appears in column a. Column b and c autofill even though the cell contains a drop down. I am ok with doing it manually but my thought was a tundra is always going to have that category and subcategory. Hopefully that is a little clearer. Sorry for the confusion. Thank you.

**ChemistB** · 08-09-2012, 05:04 PM

Can you load (Go advanced> manage attachments) a dummy file showing how your lists are set up and how you want things to happen?

**shawnsonline** · 08-10-2012, 03:51 PM

I am sorry for the delay. I hope I did the attachment correctly. Thanks again

**ChemistB** · 08-10-2012, 04:53 PM

I entered this equation in B8. It can be dragged across and down as far as you like

=IFERROR(VLOOKUP($A8,$A$1:$C7,COLUMN(B1),FALSE),"")
Does that work for you?