Excel and Dropdown boxes!

NickyW

Hi there
I was just wondering if there are any excel experts out there who could help me or point me in the right direction.

I want to create a spreadsheet with dropboxes,

In the first column would be say product type.

In the second column would be another dropbox which would bring up other categories according to which was chosen in column A.

eg Column A ProductType - Cats
Dogs

Column B -(if Cats was chosen) would bring up tabby and ginger. If Dogs was chosen would bring up labrador and terrier.

I hope I am making sense! Any replies would be greatly appreciated.

dantray

That sounds a bit tricky. I suspect it's more a Visual Basics task than standard excel. It's not so much the drop downs but rather getting your cell in column B to point towards a different predetermined list depending on the option selected in column A.

You may be better posting your question in http://www.excelforum.com/... These guys are crazy!

Also, will you update this with the solution - I am quite interested to know the answer!

Cheers.

villapb

Hi you have to data validation to create the drop downs and hlookup to create the link. You make a table with The rows Dog, Westy, Geyhound etc, Then the next row Cat, Persian etc......then you name the dog column ie animals and then then name whole table ie animal type. Best to use table instead of ranges as they are dynamic and the drop down auto update when you add to the tables later on....pick a cell and use data validation using animals as the list press f3, then the next adjacent call us the hlookup using animals type as the list again f3 pulls it up.............takes a couple if attempts to get it right.

villapb

Nah dan vlookups, hlookups, indirect, data valiadtion so great when you get used to then and advanced filter.........shuts up the accas anyway when i show them lol..........

jamesm96

Like Dantray says, I think the Excel forum is your best bet. I just wonder, though (I'm not near a PC so I can't check), when you enter data validation, you can select 'list' as an option, and you can either type a list straight into the validation window, or you can point it to a range if cells. Could you not make the content of that range of cells vary depending on the data in the A column?

I reckon you could use a lookup with a table of all the categories and options, so that your validation list is actually a host of lookups, looking up whatever's been selected in column A to return a value from the table.

I'm possibly not explaining it very well, and maybe it doesn't work, but I'll have a play tomorrow. It'd get substantially more cumbersome if you wanted a third variable column though, lol.

villapb

Hi James the third cell isnt cumbersome tbh thats where indirect comes in,,,,,,,,,,,,,formula is =hlookup the click the animals cell(table is the animals type, 2 which looks in the table at col 2,0) the zero make it an exact match if it it was 1 instead it would be variable. S what is happening is excel is using the animals cell to link the animals type using the table ...........hlookup is a horz search, vlookup is a vertical search.........

jamesm96

Ah! Lol, I was writing my reply on my phone. It took me so long that by the time I'd finished it you'd already written your reply!

ExcelAnt

Woah ppl!!

Not sure i've followed this correctly but i've no idea why your referencing Hlookups. Indirect data validation will do the trick.
Follow this:
http://www.contextures.com/xlDataVal02.html

Any trouble ask

The lists need to be perfect references (including upper lower case)

Hopefully Nicky W finds this

NickyW

Guys - thank you so much for your replies. I am going to work on this tomorrow - so will let you know how I get on!! Thanks!

NickyW

I have done it! By using indirect data validation - thanks for pointing me in the right direction.

Now I have done it I want to add more entries to some of my lists - the only problem I have is I don't know how to change the range. eg if 1 list was cars, bikes and boats - I would highlight these click in the name box and type say 'transport'. If I then wanted to say add planes - I would have to change the range for transport to include planes and I can't seem to be able to do this!
Hope I am making sense!

Pegasus

Hi NickyW,

In Excel 2010 go to formulas and somewhere around the middle is Name Manager. Select this and you should get a pop up window with all of your named ranges. Select the name you want to change and the cell range should come up below. Change the range to what you want to include and remember to click the tick to confirm.

In Excel 2003 you go to insert/name/define and select the named range you want to change and then change the cell range to iclude what you want to include. If I recall correctly you just need to then click add.

Bluewednesday

villapb wrote: »

...shuts up the accas anyway when i show them lol..........

When I did my ACCA exams there was no spreadsheets in it so I'm not sure why they should know any more than you?

villapb

Maybe i was just joking...........did they do sense of humour banter etc when you did ACCA...........IS YOUR OFFICE VERY SERIOUS............ours isn,t.....

Bluewednesday

Sorry my bad, didn't realise it was a funny joke