Hi all,

Better way of describing this. Say I have 2 columns countries/cities.

I want to be able to select say England and France in one column then have all cities related to them both in the next column.

I can already select 2 items in 1 cell, and do a dependancy based on the first item selected in that cell.... However not both items in the cell.

Editted the description, have looked all over for this and still no luck, anyone have any ideas? Have the descriptions been okay/understandable?

This is not a trivial implementation of dependant DV dropdowns. I use a
similar approach in my 'Invoicing: Simple Bookkeeping' for selecting
ChartOfAccount items/subitems. The structure I use is what I call
"T-bone" lists, where the accounts are a vertical list and subaccounts
run horizontal off their respective parent account. The accounts list
is a fixed defined name range. The subaccounts are dynamic defined name
ranges. The formula I use in the subaccount column DV list Source is...

=INDIRECT(SUBSTITUTE(ExpenseCategory," ",""))

...where 'ExpenseCategory' is a col-absolute/row-relative defined name
with local scope. This formula looks identical to yours, and is used to
remove spaces in the Category name because this is what's used for the
subaccount dynamic range defined names for the INDIRECT function.

If you can't see your dependant lists then I suspect your list ranges
are properly defined. I think you'll have to implement a more complex
methodology using multiple dependant DVs, but it shouldn't be too
difficult if you use multiple lists...

1st T-bone: Lists A:B
2nd T-bone: Lists B:C
3rd T-bone: Lists C:D

...and so on. This is the approach I'd use if my subaccounts had
subaccounts! There's no reason this won't work if you store all your
lists on a separate sheet, AND name them properly for use in their
respective DV list Source formulas. I'd go with the same naming
convention but append an index to the subsequent uses. (ie: Bank
Charges, Bank Charges2) This will cause the 3rd dependant list to
contain the items in the range named "BankCarges2" rather than the
items in the "BankCharges" range.

HTH

--
Garry

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

Oops! A few typos...


This is not a trivial implementation of dependant DV dropdowns. I use

a similar approach in my 'Invoicing: Simple Bookkeeping' app for
selecting Chart Of Accounts items/subitems.

The structure I use is what
I call "T-bone" lists, where the accounts are a vertical list and
subaccounts run horizontal off their respective parent account. The
accounts list is a fixed defined name range. The subaccounts are
dynamic defined name ranges. The formula I use in the subaccount
column DV list Source is...

=INDIRECT(SUBSTITUTE(ExpenseCategory," ",""))

..where 'ExpenseCategory' is a col-absolute/row-relative defined name

with local scope that points to its dependant list.

This formula looks identical to yours, and is used
to remove spaces in the Category name because this is what's used for
the subaccount dynamic range defined names for the INDIRECT function.

If you can't see your dependant lists then I suspect your list ranges

are NOT properly defined.

I think you'll have to implement a more complex
methodology using multiple dependant DVs, but it shouldn't be too
difficult if you use multiple lists...

1st T-bone: Lists A:B
2nd T-bone: Lists B:C
3rd T-bone: Lists C:D

..and so on. This is the approach I'd use if my subaccounts had
subaccounts! There's no reason this won't work if you store all your
lists on a separate sheet, AND name them properly for use in their
respective DV list Source formulas. I'd go with the same naming
convention but append an index to the subsequent uses. (ie: Bank
Charges, Bank Charges2) This will cause the 3rd dependant list to

contain the items in the range named "BankCharges2" rather than the

items in the "BankCharges" range.

HTH

--
Garry

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