fittons_coaching_badge
Offline
Posts: 2059
|
|
« on: Sunday, April 7, 2019, 16:01:05 » |
|
I trying to create a multi dependant drop down list in excel based on products where selecting one option in column A restricts column B options dependant on column A and so on.
I have googled this and it say to create mini tables and name them which is fine, but all the examples seem to have just 1 word.
My products have 2 word names for example, which it doesn't seem to like when I try and name the mini table.
That probably makes no sense to most but it is does to you any advice much appreciated …..
|
|
|
Logged
|
|
|
|
Bob's Orange
Has brain escape barriers
Offline
Posts: 28578
|
|
« Reply #1 on: Sunday, April 7, 2019, 17:00:46 » |
|
Could you put the two words together to make one? Or use an underscore or a dot or something to pull the words together?
I’m sure there is a better way but I’m crap with detailed excel.
|
|
|
Logged
|
we've been to Aberdeen, we hate the Hibs, they make us spew up, so make some noise, the gorgie boys, for Hearts in Europe.
|
|
|
jutty274
Offline
Posts: 1863
|
|
« Reply #2 on: Monday, April 8, 2019, 07:54:59 » |
|
I would suggest the same as Bob, just out of interest do any of your products have the same first name i.e. Microsoft Word, Microsoft Excel; because if they do that could confuse Excel into thinking that you have duplicated products as there would be no single identifiable characters. The other way may be to put a table in the document as a reference and number each product, then just use the numbers in the drop down menu table, I know this sounds long winded but as good as Excel is it does have it's limitations.
|
|
|
Logged
|
|
|
|
4D
Or not 4D that is the question
Offline
Posts: 21952
I can't bear it 🙄
|
|
« Reply #3 on: Monday, April 8, 2019, 13:40:44 » |
|
When you create a table look at the design tab, insert slicer and choose both columns. Does that help?
|
|
|
Logged
|
|
|
|
swindonmaniac
Offline
Posts: 3866
|
|
« Reply #4 on: Monday, April 8, 2019, 13:54:02 » |
|
I would suggest the same as Bob, just out of interest do any of your products have the same first name i.e. Microsoft Word, Microsoft Excel; because if they do that could confuse Excel into thinking that you have duplicated products as there would be no single identifiable characters. The other way may be to put a table in the document as a reference and number each product, then just use the numbers in the drop down menu table, I know this sounds long winded but as good as Excel is it does have it's limitations.
Bloody hell Jutty didn't realise you were that intelligent. Town End Mastermind.
|
|
|
Logged
|
Life is a Rollercoaster - Just gotta ride it.
|
|
|
Ginginho
Offline
Posts: 6864
|
|
« Reply #5 on: Monday, April 8, 2019, 14:39:17 » |
|
Using Define Names and Data Validation would work.
For example.
If you have a top level list - Groceries, for example (see attached)
If you wanted a first drop down box to select type of grocery (Fruit, Vegetable, Meat) and the next drop down box to automatically default to a list of that type of grocery.
Give your lists names by going to "Define Name" under the Formulas ribbon.
So, items in red, call them "Groceries", items in brown "Fruit", and so on.
Then select the cell you want your first list to be (my example - A8) Go to "Data Validation" under the Data/Data Tools ribbon. Under setting, select list and source put in the name (=Groceries)
This should give you the drop down list showing Fruit, Vegetable and Meat.
Next, put a drop down box in the next cell (B8), select Data Validation again, List, and the source will be =INDIRECT($A$8)
So, when you now select an option from the groceries section, it will then narrow your next selection down to that grocery type.
|
|
|
|
jayohaitchenn
Wielder of the BANHAMMER
Offline
Posts: 12534
|
|
« Reply #6 on: Monday, April 8, 2019, 15:42:44 » |
|
None of your examples use 2 words in a single cell...
|
|
|
Logged
|
|
|
|
Bewster
Offline
Posts: 4004
We fucking love you Gumbo!
|
|
« Reply #7 on: Monday, April 8, 2019, 15:45:47 » |
|
Thank Ging - learn something new everyday. I thought I was pretty good at excel
|
|
|
Logged
|
|
|
|
Ginginho
Offline
Posts: 6864
|
|
« Reply #8 on: Monday, April 8, 2019, 16:49:41 » |
|
None of your examples use 2 words in a single cell...
Doesn’t matter, it’ll work the same way
|
|
|
Logged
|
|
|
|
jayohaitchenn
Wielder of the BANHAMMER
Offline
Posts: 12534
|
|
« Reply #9 on: Monday, April 8, 2019, 20:23:03 » |
|
Fair enough. I will try it at some point, I love a nice drop down list in Excel.
|
|
|
Logged
|
|
|
|
|