Fix issue with removing duplicates in Data Validation and using Dependent Data Lists

A. A picture of a sheet is useless. No-one is going to re-type your stuff and we can't see what you did.

B. Are you trying to use INDIRECT between workbooks? If so, it ONLY works if both are open.

C. Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)?

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Depending on your answer to the next question... you may be able to do this using formulae. However, looking at your data, i suspect that you'll need VBA (or a miracle... or both).

the question.

How many rows (MAXIMUM) in Main do you want to use dropdown boxes in?

Given the number of choices in your first four columns ALONE would require you to set up more than 9000 tables, using "conventional" aproaches. A TOTAL non-starter.

However, if you need only 10 rows (can you refine that downwards at all??, as each one has to be done individually), this might be a runner.

Take a look at this. It follows the same sort of structure that you have.

1. Does this model (not tailored for you - just one i prepared earlier) fit your need?

2. Do you have enough Excel to adapt it to your scenario?

3. if so, good luck & shout if/when you hit a wall...

4. if not, well... ask me nicely...

You'll see that it's macro-enabled. I have a bit of VBA there to clear "upstream" boxes, should you change a "downstream" choice.

Glenn,

I appreciate the help. This solution seems to be exactly what I am looking for.

I am running into one problem though. In some instances 4th dropdown column is producing the wrong dropdown data than what it should.

I attached the file I am testing with.

Edit: I think I spotted what's causing the problem. Are you just matching the adjacent columns with each other, like column 1 with column 2 and column 2 with column 3,...etc. Wouldn't they all need to match with each other for it to work?

Ooops. Your reply got lost. Only spotted this now. Try this...

My www access is awful at the moment. I'm off the beaten rtrack in western France... I'm now a bit confused. My original understanding was that you wanted 5 columns and maximum 10 rows. Now you are talikng about 3 columns?? or what??

can yu be abolutely explicit about what you need?

Getting a specification here is like trying to get blood from a stone.... WHICH TWO COLUMNS DO YOU WANT TO USE? Depending on the number of combinations, it might be easier to use an alternative approach.