Excel and Dropdown boxes!
NickyW
Registered Posts: 97 Regular contributor ⭐
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.
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.
0
Comments
-
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.0 -
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.0
-
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..........0
-
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.0 -
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.........0
-
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!0
-
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 this0 -
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!0
-
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!0 -
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.0 -
-
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.....0
-
Sorry my bad, didn't realise it was a funny joke0
Categories
- All Categories
- 1.2K Books to buy and sell
- 2.3K General discussion
- 12.5K For AAT students
- 318 NEW! Qualifications 2022
- 161 General Qualifications 2022 discussion
- 11 AAT Level 2 Certificate in Accounting
- 56 AAT Level 3 Diploma in Accounting
- 87 AAT Level 4 Diploma in Professional Accounting
- 8.8K For accounting professionals
- 23 coronavirus (Covid-19)
- 272 VAT
- 92 Software
- 273 Tax
- 135 Bookkeeping
- 7.2K General accounting discussion
- 200 AAT member discussion
- 3.8K For everyone
- 38 AAT news and announcements
- 345 Feedback for AAT
- 2.8K Chat and off-topic discussion
- 582 Job postings
- 16 Who can benefit from AAT?
- 36 Where can AAT take me?
- 42 Getting started with AAT
- 26 Finding an AAT training provider
- 48 Distance learning and other ways to study AAT
- 25 Apprenticeships
- 66 AAT membership