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?

 

[russkris]

I have googled my brain out and can not seem to find a way to do it.

 

[easy2understandexcel]

Not sure if you are adequately explaining what you are trying to do...
If B2 = "Far North West" then you want C2 to = "GAFarNW"?

If so, let me know because its pretty simple, youre just going about it the wrong way...

 

[russkris]

Not sure if you are adequately explaining what you are trying to do...
If B2 = "Far North West" then you want C2 to = "GAFarNW"?

If so, let me know because its pretty simple, youre just going about it the wrong way...

Apologies for that.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Header
[/TD]
[TD]Header
[/TD]
[TD]Header
[/TD]
[TD]Header
[/TD]
[TD]Header
[/TD]
[TD]Header
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Data
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Menu
[/TD]
[TD]Data validation
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

So in Column 'D' there is a small list of 12 items, when you select one of them will depend on the options in Column 'E'
Column 'D' will be List =MarinePlan - Which has 12 Plans
Column 'E' has GABlack, GADentre, GAFarNW, GAGeorge, GAGreatOys, GAHuon, GAPipe, GAPitt, GAPortS, GATasPen, GAWild, GANoPlan

Does that help any more?

 

[russkris]

Sorry not sure how to edit posts.

So yes, if the user selects "Far North West" from Column D it will show the results to GAFarNW in E.

 

[FDibbins]

Hard to explain without showing the tables, but here goes. I will use an example I made up for training, so adjust as needed.

It all starts with tables containing your DD values, and using named ranges...
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][/tr]
[tr][td]

2​

[/td][td]Type[/td][td]Mammal[/td][td]Bird[/td][td]Fish[/td][td]Bug[/td][td][/td][td]Dog[/td][td]Cat[/td][td]Fresh Water[/td][td]Marine[/td][/tr]

[tr][td]

3​

[/td][td]Mammal[/td][td]Dog[/td][td]Domestic[/td][td]Fresh Water[/td][td]Insect[/td][td][/td][td]Doberman[/td][td]Lion[/td][td]Trout[/td][td]Shark[/td][/tr]

[tr][td]

4​

[/td][td]Bird[/td][td]Cat[/td][td]Wild[/td][td]Marine[/td][td]Arachnid[/td][td][/td][td]Poodle[/td][td]Tiger[/td][td]Minow[/td][td]Barracuda[/td][/tr]

[tr][td]

5​

[/td][td]Fish[/td][td]Cow[/td][td][/td][td][/td][td][/td][td][/td][td]bulldog[/td][td]House[/td][td]Pike[/td][td][/td][/tr]

[tr][td]

6​

[/td][td]Bugs[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]
Row 2 contains the range names I used
A contains the top-level categories
B:E contains sub-level1 categories...Dog, Cat, Cow are all part of Mammals
G:H contains sub-level2 categories...Doberman, Poodle, bulldog are all part of Dogs
etc

For the extract...
[Table="width:, class:grid"][tr][td] [/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr]
[tr][td]

3​

[/td][td]

Type​

[/td][td]

Category​

[/td][td]

Breed​

[/td][/tr]

[tr][td]

4​

[/td][td]Mammal[/td][td]Dog[/td][td]Doberman[/td][/tr]
[/table]
B4:D4 are the dependent drop-downs, and are created using Data Validation (on the Data Tab)
B4 Click DV/select Allow = List/Source =Type
Remember, I called the level 1 Cat Type
C4 and C5 get a bit tricky. Both use Allow = List
C4 Source =INDIRECT(SUBSTITUTE(B4," ","_"))
D4 Source =INDIRECT(SUBSTITUTE(C4," ","_"))

Hope that made sense?

 

[russkris]

I will try to digest that.. I am not across the INDIRECT and SUBSTITUTE.

All the lists are setup in Name manager on a sheet called "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))))))))))))

Bit of a long one.. I will try the INDIRECT and SUBSTITUTE, or atleast try to understand it..lol

 

[russkris]

Hi Ford,

I have tried to digest your information but I am just stuck. I tried a test on cell B20/21
In B20 I put =MarinePlan - Works fine
In B21 I put =INDIRECT(SUBSTITUTE(B20," ","_")) - Nothing populates

Would you please be able to help any further?

 

[FDibbins]

The reason I use SUBSTITUTE(B4," ","_") is in case your name has a space in it - range names don't like spaces - if there are no spaces, it is not needed, but should not cause a problem if left in.

OK, your formula is looking for a range called MarinePlan...does that exist? If not, and it contains 2nd (or 3rd) tier choices, then you need to create it

What is actually happening here is that the excel needs data to use in it's search, but it cannot use text, so we use INDIRECT to convert plain text into something that excel can use in a formula

 

[russkris]

oh yes sir, I have the name ranges done.

Below is a small section from my 'lists' sheet.
Blue text is name range 'MarinePlan'
Orange text is name range 'GABlack'
Green text is name range 'GAEntre'
and so on. (Red text is to remind me what each list is named)

So on sheet 'Cases' column 'D' I would like the =MarinePlan to display, which it does, but am falling over for Column 'E', when you select "Blackman Bay" nothing appears from the Blackman Bay list.

[TABLE="class: grid, width: 675"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD] =MarinePlan
[/TD]
[TD]Blackman Bay[/TD]
[TD]D'Entrecasteaux Channel[/TD]
[/TR]
[TR]
[TD]Blackman Bay
[/TD]
[TD]Blackman Bay
[/TD]
[TD]Cloudy Bay Lagoon
[/TD]
[/TR]
[TR]
[TD]D'Entrecasteaux Channel[/TD]
[TD][/TD]
[TD]Fleurty's Point
[/TD]
[/TR]
[TR]
[TD]Far North West[/TD]
[TD][/TD]
[TD]Great Bay (Bruny)
[/TD]
[/TR]
[TR]
[TD]Georges Bay[/TD]
[TD][/TD]
[TD]Hastings Bay[/TD]
[/TR]
[TR]
[TD]Great Oyster Bay and Mercury Passage[/TD]
[TD][/TD]
[TD]Little Taylors Bay[/TD]
[/TR]
[TR]
[TD]Huon River and Port Esperance[/TD]
[TD][/TD]
[TD]Long Bay Reef - Not Active
[/TD]
[/TR]
[TR]
[TD]Pipe Clay Lagoon[/TD]
[TD][/TD]
[TD]Recherche Bay[/TD]
[/TR]
[TR]
[TD]Pittwater[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Port Sorell[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tasman Peninsula and Norfolk Bay[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Wild Area's[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]No Plan
[/TD]
[TD]GABlack
[/TD]
[TD]GADentre[/TD]
[/TR]
</tbody>[/TABLE]


Sorry to be such a pain.