Hi Everyone,

I have a worksheet with a two column list on it. It looks like this:
A B
_____________________________
MakeA Red
MakeA Green
MakeA Blue
MakeB Orange
MakeB Cyan
MakeB Blue
MakeC Purple
MakeC Violet
MakeC Red

and so on.

I have a dynamic named range for each of the columns, 'Make' and 'Model'.

What I would like to do is to create a dynamic data validation list based on the selection of 'Make' so that the appropriate 'Model's are listed in the dropdown.

I have seen a number of different approaches to this on the net, but have not had any success in implementing them. Often the explanations of their workings are convoluted and confusing.

An example use would look like this:

Cell A1 has a data validation dropdown that lists the possible makes (I have a separate list that has unique entries one for each make). The user selects the 'Make' they want and the cell B1 has a data validation dropdown which lists only the models appropriate to that 'Make'.

Can anyone help me create the dynamic data validation dropdown?

Cheers

The Frog

Look here...

http://www.contextures.com/tiptech.html#Go_D

...and expand *+Validation* to view *Dependant Lists*.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

typo...

Look here...

http://www.contextures.com/tiptech.html#Go_D

..and expand *+Validation* to view *Dependent Lists*.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi GS,

Thanks for the link. Excellent resource. I will have a play with the options it provides and see if I can find a workable solution.

Cheers

The Frog

Hi GS,

I have a semi-working solution based on the following:

=OFFSET(Brand,MATCH($C2,Manufacturer,0)-1,0,COUNTIF(Manufacturer,$C2))

With the Manufacturer / Brand lists the entries may not be sorted. I can do this manually of course, however I am wondering if there is a way to handle the lists in an unsorted way. The same two columns apply, Manufacturers (Make) in one column, and Brand (Model) in the adjacent column. If Manufacturer A has an entry interspersed with Manufacturer B, or Manufacturer C what I am receiving back in the Brand (Model) validation list is the correct number of entries, but the entries are from a contiguous set of cells in the Brand (Model) column rather than the scattered individual entries relevant to the Manufacturer.

I think this can be solved with an array formula but I am unsure how to proceed with this. Do you have any ideas?

Cheers

The Frog

EDIT: The formula is for the data validation. The 'Brand' and 'Manufacturer' are dynamic named ranges.

The data looks like:

A 1
A 2
A 3
B 4
B 5
B 6
A 7
B 8
C 9

And so on... where the first column is the Manufacturer named range and the second column is the Brand named range.

Here's how I use this methodology...

1st DV col lists Chart of Accounts (CoA);
-this is a vertical dynamic named range in ColA of a sheet named
"Lists"

Running horizontally off each item in the CoA are sub-accounts. Each
item, then, is also the name of a horizontal dynamic range.

Now, the CoA is divided into 3 main sections; Income, CostOfSales, and
Expenses. The Income list contains sales categories. The Expenses list
is further divided into OperatingExpenses and OtherExpenses.

So to give example...
A | B | C | D | E |...and so on
Income | Revenue1 | Revenue2 | Revenue3 | Income:Other
Cost Of Sales | Freight In | Freight Out | Delivery Expense | COS:Other
Expenses
Administration | Admin Fees | Management Fees | Admin:Other
Insurance | Building | Liability | Insurance:Other
Taxes | Business | Property | Taxes:Other
Bank Charges | Fees | Interest | Bank Charges:Other
OtherExpenses
Web And Internet | Admin Charges | Internet Service | Web:Other
Vehicle Expense | Repairs | Insurance | Fuel/Oil : Vehicle:Other

...where each named range is contiguous. Names are the CoA list minus
spaces so I can use the following formula in the sub-account DV...

=INDEX(SUBSTITUTE(ExpenseCategory," ",""))

...so when I select 'Bank Charges' on the Expenses sheet its dependent
DV lists the 3 items in the dynamic range named "BankCharges". The 1st
DV is in a column-absolute, row-relative range named "ExpenseCategory"
on the Expenses sheet. Its DV formula is "=ExpenseCategories" as that's
the name of the Expenses section of the CoA.

Suggestion:
List the manufacturers in colA, and list models for each manufacturer
horizontally as exampled above.

Name colA dynamic "Makes".
Name the models lists the manufacturer.

In your sheet 1st DV "=Makes".
In your 2nd DV "=INDEX(SUBSTITUTE(Make," ",""))", and name that col
"Make" so it's row-relative.

Note that these DV dynamic names need to have workbook level scope, BUT
make all sheet named ranges local scope (sheet level)!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

I have a semi-working solution based on the following:

=OFFSET(Brand,MATCH($C2,Manufacturer,0)-1,0,COUNTIF(Manufacturer,$C2))

With the Manufacturer / Brand lists the entries may not be sorted. I can do
this manually of course, however I am wondering if there is a way to handle
the lists in an unsorted way. The same two columns apply, ...

If this is tangential or redundant, forgive the digression.

There's a way to create a dynamic data validation list using only Excel formulas. The approach does use a lot of work space for intermediate results, though. It starts from the long two-column list, dynamically builds a two-dimensional "Make" vs. "Color" matrix in an out-of-the-way place, and uses the matrix for the data validation formulas.

It's not elegant and it doesn't use named ranges, but it does seem to fill the need.

For visual clarity, my example uses one worksheet, but the work can be split among two or more using the same basic approach.

Columns A:B hold the original data. Column A contains the "Make" values; B, the "Colors." Duplicated Make values need not be contiguous.

D1 will have data validation for Makes; D2, for Colors.

In F1, put the number 1.

In F2, put
=IF(A2="","",IF(COUNTIF(A$1:A2,A2)1,"",MAX($F$1:F 1)+1))
and copy down past the end of the original list.

In H1, put
=IF(ROW()MAX(F:F),"",INDEX(A:A,MATCH(ROW(),F:F,0) ))
and copy down as far as before.

In I1 put
=IF(H1="","",COUNTIF(A:A,H1))
and copy down as far as before.

In G1, put
=IFERROR(100*MATCH(A1,H:H,0) + COUNTIF(A$1:A1,A1),"")
and copy down as far as before.

In J1, put
=IFERROR(INDEX($B:$B,MATCH(100*ROW()+COLUMN()-9,$G:$G,0)),"")
and copy down as far as before.

Select the formula-containing cells in column J, and copy them rightward more columns than the largest number of Colors for any Make.

In D1, use the data validation formula
=OFFSET($H$1,0,0,MAX(F:F),1)

In D2, use the data validation formula
=OFFSET(I1,MATCH(D1,H:H,0)-1,1,1,VLOOKUP(D1,H:I,2,FALSE))

Changing columns A:B should update the data validations without other intervention.

(I have Excel 2010.)

Is this what you're trying to achieve? Your question was answered on the second post.

http://www.contextures.com/xlDataVal13.html