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


+2/-9
Offline Offline

Posts: 2044




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


+40/-13
Offline Offline

Posts: 18990





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


+0/-1
Offline Offline

Posts: 1697




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


+96/-88
Online Online

Posts: 12316


I am real




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


+25/-22
Offline Offline

Posts: 737





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

English & Proud.
Pounds  & Ounces not kilos & grammes, mpg not kilometres & litres, feet & inches not mm & cm (whatever the fuck they are)  - Fuck Europe !!!!!!.
Ginginho


+27/-7
Offline Offline

Posts: 6129





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 23 times.)
Logged
jayohaitchenn
Wielder of the BANHAMMER


+57/-89
Offline Offline

Posts: 10765




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

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


+10/-1
Offline Offline

Posts: 3899


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


+27/-7
Offline Offline

Posts: 6129





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


+57/-89
Offline Offline

Posts: 10765




« 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: