Multiple items selected multiple dependancies available

[Harryt1pper]

Hi all,

I am setting up an excel table that allows for multiple dependants, i.e
cell D is dependant on C which is dependant on B which is dependant o
A... This is working using the following functio
=INDIRECT(SUBSTITUTE(B5," ","")), =INDIRECT(SUBSTITUTE(C5," ",""))
=INDIRECT(SUBSTITUTE(D5," ","")) where D is the column and 5 is th
row.
This section succesfully works, to those that are curious the indirec
function creates the dependancy whilst the substitute function allow
for multi-word named lists.

I have also succesfully set up a multiple asset selection per cell, fo
instance if I select colours in A then I can select say red in B but
can also select blue in B and would see the following red, blue. Agai
for those who are curious I used this website for thi
http://www.contextures.com/excel-data-validation-multiple.html and use
the code there. If you want to repeat this for multiple columns selec
the areas that have been highlighted with red arrows copy paste them an
enter in the relevant column number. I have done this for columns 11-1
in mine which is L-R.

NOW MY PROBLEM
I want to combine the two. Currently when I click say (Cell A1) Unit
get my unit options Red, Blue, Green. I now choose Red (Cell B1) whic
means cell C1 offers me toilets, showers, sinks. I now also choose blu
in Cell B1 and both red and blue show. However when I go to cell C1
still only see the options that follow from having chose red and do no
see the variables produced by blue.

Does anyone have a way around this/done anything similar? I wil
continue to search the internet sorry if my technical jargon is behin
on this I have only really started using excel these past two days fo
anything serious after being handed a massive database that need
organising.

Thanks in advance and looking forwards to responses - sorry for the lon
windedness

 

[Harryt1pper]

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

 

[GS]

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

...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

 

[GS]

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

..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