Pages: [1]   Go Down
Print
Author Topic: New Excel Query  (Read 4328 times)
fittons_coaching_badge

Offline Offline

Posts: 2059




Ignore
« 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 Offline

Posts: 28446





Ignore
« 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 Offline

Posts: 1863




Ignore
« 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 Offline

Posts: 21799


I can't bear it 🙄




Ignore
« 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 Offline

Posts: 3813




Ignore
« 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 Offline

Posts: 6848





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






* Excel.jpg (31.47 KB, 304x247 - viewed 284 times.)
Logged
jayohaitchenn
Wielder of the BANHAMMER

Offline Offline

Posts: 12507




« Reply #6 on: Monday, April 8, 2019, 15:42:44 »

None of your examples use 2 words in a single cell...
Logged
Bewster

Offline Offline

Posts: 4004


We fucking love you Gumbo!




Ignore
« Reply #7 on: Monday, April 8, 2019, 15:45:47 »

Thank Ging - learn something new everyday. I thought I was pretty good at excel  Clap
Logged
Ginginho

Offline Offline

Posts: 6848





Ignore
« 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 Offline

Posts: 12507




« 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
Pages: [1]   Go Up
Print
Jump to: