Data Validation List with 12 sub lists

[russkris]

Hi,

I am trying to set up a data validation over 2 columns(B+C)

I have the B setup fine but the C column wont allow the amount of 'sub' lists.

=IF(B2="No Plan",GANoPlan,IF(B2="Far North West",GAFarNW,IF(B2="Port Sorell",GAPortS,IF(B2="Georges Bay",GAGeorge,IF(B2="Great Oyster Bay and Mercury Passage",GAGreatOys,IF(B2="Blackman Bay",GABlack,IF(B2="Tasman Peninsula and Norfolk Bay",GATasPen,IF(B2="Pittwater",GAPitt,IF(B2="Pipe Clay Lagoon",GAPipe,IF(B2="D'Entrecasteaux Channel",GADentre,IF(B2="Huon River and Port Esperance",GAHuon,IF(B2="Wild Area's",GAWild))))))))))))

Is there any way to make this easier?

 

[FDibbins]

Sorry to be such a pain.

not at all

You have a list called Blackman Bay?
You have data in that named range?

Remember, that each name selected needs to have a matching named range with data in it.

So if your 1st tier is Animals and contains mammal, Bird, Fish...
If you select Mammal, you need a range name Mammal, and that needs to contains data
Mammal contains Dog, Cat, Cow, Bear etc
You select Dog
You need a range name called Dog, and that needs data in it
Dog contains Poodle, Sheppard, pom, Doberman

You select Mammal, tier 2 gives options of Dog, Cat, Cow
You select Dog, tier 3 gives options of Poodle, Sheppard, pom, Doberman

You have probably already done all this, but just making sure we are on the same page here, with nothing left out

 

[russkris]

No sir the 'lists' are name the GAxxxx as it doesn't accept space and Illegal characters etc. so I named them GAxxxx

So how do I name the lists the same as the 'marineplan' list?

 

[FDibbins]

As I said, the name of each range needs to match the item selected

Select Mammal...options = Dog, Cat, etc
You now need to have named ranges called Dog, Cat and Cow (not GAdog, GAcat, GAcow)
Select Dog...options = Poodle, Sheppard, pom, Doberman
You now need named ranges called Poodle, Sheppard, pom and Doberman

 

[russkris]

not at all

You have a list called Blackman Bay?
You have data in that named range?

Remember, that each name selected needs to have a matching named range with data in it.

So if your 1st tier is Animals and contains mammal, Bird, Fish...
If you select Mammal, you need a range name Mammal, and that needs to contains data
Mammal contains Dog, Cat, Cow, Bear etc
You select Dog
You need a range name called Dog, and that needs data in it
Dog contains Poodle, Sheppard, pom, Doberman

You select Mammal, tier 2 gives options of Dog, Cat, Cow
You select Dog, tier 3 gives options of Poodle, Sheppard, pom, Doberman

You have probably already done all this, but just making sure we are on the same page here, with nothing left out

Hmm.. This might be where we come unstuck.

As the names on the '=MarinePlan' List are correct but the name manager will not allow spaces or illegal characters.

So if somone selects 'D'Entrecasteaux Channel' and wants to select 'Great Bay (Bruny)'.
Name manager will not allow me to name a range with "D'Entrecasteaux Channel " as it has spaces and " ' ".

So maybe it can not be done

 

[FDibbins]

If the names contain spaces, that is not a problem. That is exactly why I included the SUBSTITUTE() in there. Just make sure that you use the exact same name, but swap the space with a _ (underscore)

Great Bay (Bruny)
would become
Great_Bay_(Bruny)

 

[russkris]

Ahhhhh. Light bulb moment.
So the user will see the "marineplan" list and they won't see underscores but the list will find it.

So I need to amend the 'GABlack' to 'Blackman_Bay' in the name manager and the user will see the list options

 

[russkris]

Ahhhhh. Light bulb moment.
So the user will see the "marineplan" list and they won't see underscores but the list will find it.

So I need to amend the 'GABlack' to 'Blackman_Bay' in the name manager and the user will see the list options

Wowzar... Done it.. Thank you very very much. Another one for the memory bank.

One last thing. Can the substitute work with (') apostrophes, tried adding "'" [=INDIRECT(SUBSTITUTE(C20," ","_","'"))]
doesn't seem to work.

 

[FDibbins]

I am really happy I was able to explain this to you

Mine=INDIRECT(SUBSTITUTE(C20," ","_"))
Yours=INDIRECT(SUBSTITUTE(C20," ","_","'"))

You have an extra (unallowed) argument in there, the SUBSTITUTE syntax is...
=SUBSTITUTE(cell-ref,what to substitute, what to substitute with)
there are only 3 arguments, you seem to have added a 4th 1, what are you trying to do there?

 

[russkris]

I am really happy I was able to explain this to you

Mine=INDIRECT(SUBSTITUTE(C20," ","_"))
Yours=INDIRECT(SUBSTITUTE(C20," ","_","'"))

You have an extra (unallowed) argument in there, the SUBSTITUTE syntax is...
=SUBSTITUTE(cell-ref,what to substitute, what to substitute with)
there are only 3 arguments, you seem to have added a 4th 1, what are you trying to do there?

Oh, I see...

So, One of the names in the Tier 1 list is D'Entrecasteaux Channel(Noting the apostrophe between the D and E)
I can take it out, replace with a space and it works but technically the apostrophe is part of the name, so I was attempting to be the smart guy and fix it myself, not knowing much about Substitute(now looking like an idiot).

I can the formula include the substitution of apostrophe's?

 

[FDibbins]

You can nest SUBSTITUTE()'s....
SUBSTITUTE(SUBSTITUTE(C20," ","_"),"'","_")